postgresql – 返回给定表的动态列集的函数

发布时间：2020-12-13 16:30:17 所属栏目：百科来源：网络整理

导读：我有一个字段表来存储其他表的列信息： CREATE TABLE public.fields ( schema_name varchar(100),table_name varchar(100),column_text varchar(100),column_name varchar(100),column_type varchar(100) default 'varchar(100)',column_visible boolean);

我有一个字段表来存储其他表的列信息：

CREATE TABLE public.fields (
   schema_name varchar(100),table_name  varchar(100),column_text varchar(100),column_name varchar(100),column_type varchar(100) default 'varchar(100)',column_visible boolean
);

我想创建一个函数来获取特定表的数据.
刚试过这样的事：

create or replace function public.get_table(schema_name text,table_name text,active boolean default true)
  returns setof record as $$

declare 
    entity_name text default schema_name || '.' || table_name;
    r record;
begin
    for r in EXECUTE 'select * from ' || entity_name loop
        return next r;
    end loop;
    return;
end
$$
language plpgsql;

使用此功能,我必须在调用它时指定列！

select * from public.get_table('public','users') as dept(id int,uname text);

我想根据public.fields表中的column_visible字段将schema_name和table_name作为参数传递给function并获取记录列表.

简单案例的解决方案

如下面引用的答案中所述,您可以使用已注册(行)类型,从而隐式声明多态函数的返回类型：

CREATE OR REPLACE FUNCTION public.get_table(_tbl_type anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('TABLE %s',pg_typeof(_tbl_type));
END
$func$LANGUAGE plpgsql;

呼叫：

SELECT * FROM public.get_table(NULL::public.users);  -- note the syntax!

返回完整的表(包含所有用户列).

等待！怎么样？

本相关答案的详细说明,章节
“各种完整的表格类型”：

> Refactor a PL/pgSQL function to return the output of various SELECT queries

TABLE foo只是SELECT * FROM foo的缩写：

> Is there a shortcut for SELECT * FROM?

完全动态返回类型的2个步骤

但是,您尝试做的事情在单个SQL命令中是完全不可能的.

I want to pass schema_name and table_name as parameters to function and get record list,according to column_visible field in
public.fields table.

没有直接的方法可以从函数或任何SQL命令返回任意选择的列(在调用时未知的返回类型). SQL要求在调用时知道结果列的数量,名称和类型.更多在相关答案的第2章：

> How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

有各种解决方法.您可以将结果包装在其中一种标准文档类型(json,jsonb,hstore,xml)中.

或者您使用一个函数调用生成查询并使用下一个执行结果：

CREATE OR REPLACE FUNCTION public.generate_get_table(_schema_name text,_table_name text)
  RETURNS text AS
$func$
   SELECT format('SELECT %s FROM %I.%I',string_agg(quote_ident(column_name),','),schema_name,table_name)
   FROM   fields
   WHERE  column_visible
   AND    schema_name = _schema_name 
   AND    table_name  = _table_name
   GROUP  BY schema_name,table_name
   ORDER  BY schema_name,table_name;
$func$ LANGUAGE sql;

呼叫：

SELECT public.generate_get_table('public','users');

这将创建一个表单查询：

SELECT usr_id,usr FROM public.users;

在第2步执行它.
(您可能希望添加列号和订单列.)
一定要防范SQL注入：

> INSERT with dynamic table name in trigger function
> Define table and column names as arguments in a plpgsql function?

旁白

varchar(100)对标识符没有多大意义,标准Postgres中限制为63个字符：

> Maximum characters in labels (table names,columns etc)

如果您了解对象标识符类型regclass的工作方式,则可以使用单个regclass列替换模式和表名.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!