PostgreSQL：将列名称放入数组PL / pgSQL [英] PostgreSQL: column names into array PL/pgSQL

问题描述

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?

推荐答案

查询应以 select 开头，不是 array ，也不必是动态SQL。

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

尝试以下修改版本：

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

这篇关于PostgreSQL：将列名称放入数组PL / pgSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！