PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select all array type along with dimension

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 ?

Tags:

2

Answers


  1. 0

    Your first example passes the actual array into the function, while your second example passes a text 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 the case. Simpler example:

    select case when false 
       then this_function_exists(but_not_with_this_type) end 
from sal_emp;

    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 of array_ndims(), which is int, or 'NA', which is text. All options in a case must be of the same type, or coercible thereto, so you need to trade that 'NA' for a null, or cast(array_ndims() as text). The former is preferable:

    case
    when data_type='ARRAY' then array_ndims(column_name)
    else null
end as array_dimension

    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. The case can be evaluated outside it so that when it’s false, the query won’t even get to have that function call in it. Demo at db<>fiddle:

    create function get_array_types_and_dims(p_schema_name text,p_table_name text)
  returns table (column_name text,
                 column_type text,
                 array_dimension int) as $f$
declare r record;
begin
  for r in select isc.column_name,isc.udt_name,isc.data_type
           from INFORMATION_SCHEMA.COLUMNS isc
           where isc.TABLE_SCHEMA like p_schema_name
           and isc.TABLE_NAME like p_table_name
           --isc.TABLE_CATALOG LIKE 'dellstore_clear'
           order by isc.ordinal_position 
  loop
  return query execute format($dyn_sql$
     select %2$L,
            case when $1='ARRAY' then $2::regtype::text
                 else $1::text
            end as column_type,
            %3$s as array_dimension
     from %1$I.%2$I
   $dyn_sql$, 
   p_schema_name,
   p_table_name,
   case when r.data_type='ARRAY' 
        then format('array_ndims(%1$I)',r.column_name)
        else 'null::int'
   end)
   using r.data_type,
         r.udt_name;
  end loop;
end $f$ language plpgsql;

select * from get_array_types_and_dims('public','sal_emp');
    column_name column_type array_dimension
    sal_emp text null
    sal_emp integer[] 1
    sal_emp text[] 2

    %1$I, %2$I, %2$L and %3$s are format() placeholders corresponding to the parameters in the parentheses, $1 and $2 are placeholders for parameters passed to execute in the using part after that.

    $f$ and $dyn_sql$ are tagged/named quotes.

    Login or Signup to reply.
  2. 0

    The following demonstrates using the system catalog pg_attribute to retrieve the number of array dimensions for each column:

    DROP TABLE IF EXISTS dims_demo;

CREATE TABLE IF NOT EXISTS dims_demo (
  id INTEGER GENERATED always AS IDENTITY PRIMARY KEY,
  a1 INTEGER[],
  a2 INTEGER[][]
);

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 a.attndims
       END AS array_dimension
  FROM information_schema.columns c
  JOIN pg_attribute a
    ON a.attrelid = (FORMAT($$%I.%I$$, c.table_schema, c.table_name))::regclass
       AND a.attname = c.column_name
  WHERE c.table_schema = CURRENT_SCHEMA()
    AND c.table_name = 'dims_demo'
  ORDER BY c.ordinal_position;

    When run, the following output is produced:

    column_name column_type array_dimension
    id integer [null]
    a1 integer[] 1
    a2 integer[] 2

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search