函数返回给定表的动态列集 [英] Function to return dynamic set of columns for given table
问题描述
我有一个字段表来存储其他表的列信息：
schema_name varchar（100），
table_name varchar（100），
column_text varchar（100），
column_name varchar（100），
column_type varchar（100）默认'varchar（100）'，
column_visible boolean
）;
我想创建一个函数来获取特定表的数据。
刚试过这样的：
$ b $ pre $ 创建或替换函数public.get_table（schema_name text，
table_name文本，
活动布尔默认值为true）
返回setof记录为$$
声明
实体名称文本默认模式名称|| '。'|| TABLE_NAME;
r记录;
在EXECUTE'select * from'||中开始
for r entity_name loop
return next r;
结束循环;
return;
结束
$$
语言plpgsql;
使用这个函数，我必须在指定列时指定列！
select * from public.get_table（'public'，'users'）as dept（id int，uname text）;
我想传递 schema_name 和<$根据中的作为参数运行并获取记录列表 column_visible 字段，将c $ c> table_name table。
简单案例的解决方案
<正如在下面引用的答案中所解释的那样，你可以使用注册的（行）类型，从而隐式地声明多态函数的返回类型：
< code $ CREATE OR REPLACE FUNCTION public.get_table（_tbl_type anyelement）
RETURNS SETOF anyelement AS
$ func $
BEGIN
RETURN QUERY EXECUTE格式（'TABLE％s' ，pg_typeof（_tbl_type））;
END
$ func $ LANGUAGE plpgsql;
RETURNS SETOF anyelement AS
$ func $
BEGIN
RETURN QUERY EXECUTE格式（'TABLE％s' ，pg_typeof（_tbl_type））;
END
$ func $ LANGUAGE plpgsql;
通话：
SELECT * FROM public.get_table（NULL :: public.users）; - 注意语法！
返回完整的表格（包含所有用户列）。
等等！如何？
在这个相关答案章节
各种完整表格类型中的详细解释：
TABLE foo 只是 SELECT * FROM foo 的简称： 2 完全动态返回类型的步骤
但是，您试图做的是 严格不可能 单个的 SQL命令。
我想要传递
schema_name和根据
public.fields 中的column_visible字段，将
没有直接的方法可以返回任意选择的列（返回类型不是在函数 - 或任何 SQL命令中都可以看到。 SQL需要在通话时知道结果列的编号，名称和类型。更多相关答案的第二章：
有各种 解决方法 。您可以将结果封装在其中一种标准文档类型中（ 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字段
WHERE column_visible
AND schema_name = _schema_name
AND table_name = _table_name
GROUP BY schema_name，表名
ORDER BY schema_name，table_name;
$ func $ LANGUAGE sql;
通话：
SELECT public.generate_get_table（'public'，'users'）;
这会创建一个形式的查询：
SELECT usr_id，usr FROM public.users;
在第二步执行它。
（您可能想要添加列数字和订单列。）
请务必防范SQL注入：
旁白
varchar（100）在标准Postgres中限制为63个字符的标识符没有多大意义：
如果您了解对象标识符类型 regclass 的工作方式，则可以用单一 regclass 栏。
I have a fields table to store column information for other tables:
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
);
And I'd like to create a function to fetch data for a specific table. Just tried sth like this:
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;
With this function I have to specify columns when I call it!
select * from public.get_table('public', 'users') as dept(id int, uname text);
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.
Solution for the simple case
As explained in the referenced answers below, you can use registered (row) types, and thus implicitly declare the return type of a polymorphic 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;
Call:
SELECT * FROM public.get_table(NULL::public.users); -- note the syntax!
Returns the complete table (with all user columns).
Wait! How?
Detailed explanation in this related answer, chapter "Various complete table types":
TABLE foo is just short for SELECT * FROM foo:
2 steps for completely dynamic return type
But what you are trying to do is strictly impossible in a single SQL command.
I want to pass
schema_nameandtable_nameas parameters to function and get record list, according tocolumn_visiblefield inpublic.fieldstable.
There is no direct way to return an arbitrary selection of columns (return type not known at call time) from a function - or any SQL command. SQL demands to know number, names and types of resulting columns at call time. More in the 2nd chapter of this related answer:
There are various workarounds. You could wrap the result in one of the standard document types (json, jsonb, hstore, xml).
Or you generate the query with one function call and execute the result with the next:
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;
Call:
SELECT public.generate_get_table('public', 'users');
This create a query of the form:
SELECT usr_id, usr FROM public.users;
Execute it in the 2nd step.
(You might want to add column numbers and order columns.)
Be sure to defend against SQL injection:
- INSERT with dynamic table name in trigger function
- Define table and column names as arguments in a plpgsql function?
varchar(100) does not make much sense for identifiers, which are limited to 63 characters in standard Postgres:
If you understand how the object identifier type regclass works, you might replace schema and table name with a singe regclass column.
这篇关于函数返回给定表的动态列集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
