PostgreSQL: column names into array PL/pgSQL

Question

create or replace function extr( tabname text ) returns text[] as
$$
declare cols text[];
begin
    execute 'array(select column_name::text from information_schema.columns where table_name = '||quote_literal(tabname)||');' into cols;
    return cols;
end;
$$
language 'plpgsql';

select extr('test');

One supplies a table name and wants back its column-names as an array. The above code gives 'syntax error at or near "array"'. How to fix this?

Daniel Vérité · Accepted Answer · 2015-09-12 17:02:30Z

3

The query should start with select, not array and it doesn't have to be dynamic SQL.

Try this modified version:

create or replace function extr( tabname text ) returns text[] as
$$
declare cols text[];
begin
    select array(select column_name::text from information_schema.columns
      where table_name = tabname) into cols;
    return cols;
end;
$$
language 'plpgsql';

answered Sep 12, 2015 at 17:02

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: column names into array PL/pgSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related