Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table public.sal_emp(
  name text,
  pay_by_quarter integer[],
  schedule text[]);
insert into public.sal_emp values
  ('name1','{1,2,3}','{{11,12},{21,22}}')
returning *;

select array_ndims(pay_by_quarter) as pay_by_quarter_dim, 
       array_ndims(schedule) as schedule_dim from sal_emp 
limit 1 ;

CREATE TABLE




name
pay_by_quarter
schedule


name1
{1,2,3}
{{11,12},{21,22}}



INSERT 0 1




pay_by_quarter_dim
schedule_dim


1
2



SELECT 1


--parser tries to select a function based on argument type
--before the db attempts to evaluate the `case` statement
select case when false then array_ndims(name) end from sal_emp;

ERROR:  function array_ndims(text) does not exist
LINE 3: select case when false then array_ndims(name) end from sal_e...
                                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


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');

CREATE FUNCTION




column_name
column_type
array_dimension


sal_emp
text
null

sal_emp
integer[]
1

sal_emp
text[]
2



SELECT 3