While using array data type in postgres, I have encountered the situation where I need to extract the dimension along with data type.
dellstore_clear=# d+ sal_emp
Table "public.sal_emp"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------
name | text | | | | extended | | |
pay_by_quarter | integer[] | | | | extended | | |
schedule | text[] | | | | extended | | |
Access method: heap
dellstore_clear=#
The dimension of a column can be retrieved using the following
dellstore_clear=# select array_ndims(pay_by_quarter) as pay_by_quarter_dim, array_ndims(schedule) as schedule_dim from sal_emp limit 1 ;
pay_by_quarter_dim | schedule_dim
--------------------+--------------
1 | 2
(1 row)
dellstore_clear=#
I am trying to combine these two while retrieving the info from INFORMATION_SCHEMA.COLUMNS
as follows,
select column_name,
case
when data_type='ARRAY' then udt_name::regtype::text
else data_type
end as column_type,
case
when data_type='ARRAY' then array_ndims(column_name)
else 'NA'
end as array_dimension
from INFORMATION_SCHEMA.COLUMNS where TABLE_CATALOG LIKE 'dellstore_clear' and TABLE_SCHEMA like 'public' and TABLE_NAME like 'sal_emp' order by ordinal_position;
But, this gives error as follows,
ERROR: function array_ndims(information_schema.sql_identifier) does not exist
LINE 7: when data_type='ARRAY' then array_ndims(column_name)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
dellstore_clear=#
dellstore_clear=#
How to pass the retrieved column name value to the subsequent functions in the same query ?
2
Answers
Your first example passes the actual
array
into the function, while your second example passes atext
value holding the name of the column.Another problem is with your
case
statement: the db will check the validity of that call before it tries to evaluate thecase
. Simpler example:That function call fails even though logically,
when false
should prevent it from ever taking place.Another issue is with the attempt use
case
to either return a result ofarray_ndims()
, which isint
, or'NA'
, which istext
. All options in acase
must be of the same type, or coercible thereto, so you need to trade that'NA'
for anull
, orcast(array_ndims() as text)
. The former is preferable:With dynamic SQL you can use the column name to construct a query that references it the way your first example does, then
execute
it. Thecase
can be evaluated outside it so that when it’sfalse
, the query won’t even get to have that function call in it. Demo at db<>fiddle:%1$I
,%2$I
,%2$L
and%3$s
areformat()
placeholders corresponding to the parameters in the parentheses,$1
and$2
are placeholders for parameters passed toexecute
in theusing
part after that.$f$
and$dyn_sql$
are tagged/named quotes.The following demonstrates using the system catalog
pg_attribute
to retrieve the number of array dimensions for each column:When run, the following output is produced:
The number of dimensions that is returned is based on the table definition. PostgreSQL does not enforce the declared number of dimensions, so an array column can hold an arbitrary number of dimensions regardless of the column type specification.