函数循环并从多个表中选择数据 [英] Function to loop through and select data from multiple tables
问题描述
我是Postgres的新手,并且拥有一个具有相同结构的多个表的数据库。我需要从每个表中选择符合特定条件的数据。
我可以用一堆 UNION
查询,但是我需要搜索的表的数量会随着时间的推移而改变,所以我不想像这样对其进行硬编码。我一直在试图开发一个函数,将循环通过特定的表(他们有一个共同的命名约定),并返回一个记录表,但我没有得到任何结果,当我查询函数。函数代码如下:
pre $ CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
RETURNS TABLE(natural_id text,name text ,natural_id_numeric文本)AS
$ BODY $
DECLARE
formal_table text;
begin
for formal_table IN
从information_schema.tables中选择table_name
其中table_schema ='public'和table_name like'formaltable%'
LOOP
EXECUTE' SELECT natural_id,name,natural_id_numeric
FROM'|| formal_table ||
'WHERE natural_id_numeric IN(
从internal_idlookup中选择natural_id_numeric
where internal_id =''7166571'')';
RETURN NEXT;
END LOOP;
返回;
END;
$ BODY $
语言plpgsql;
当我尝试使用该函数时,没有收到任何错误,但没有返回任何行:
SELECT * from internalid_formaltable_name_lookup();
有什么想法我错了?
$ func $
DECLARE
formal_table text;
BEGIN
for formal_table IN
SELECT quote_ident(table_name)
FROM information_schema.tables
WHERE table_schema ='public'
AND table_name LIKE'formaltable%'
LOOP
RETURN QUERY EXECUTE
'SELECT t.natural_id,t.name,t.natural_id_numeric
FROM internal_idlookup i
JOIN public。 formal_table || 't USING(natural_id_numeric)
WHERE i.internal_id = 7166571'; - 假设internal_id是数字
END LOOP;
END
$ func $ LANGUAGE plpgsql;
要点:
-
您必须使用 返回QUERY EXECUTE
返回每个行集。
EXECUTE
,然后是 RETURN NEXT
,完全不符合你期望的 。
-
您需要清理标识符。我在这里使用 quote_ident()
。或者你的查询将打破非标准的标识符,并允许SQL注入!
转换你的 col IN(子选择)
code>转换为更高效的 JOIN
。
-
这与使用一堆UNION查询
。它不会删除重复的行,并且实际上像 UNION ALL
。
就个人而言,我宁愿在系统目录 pg_class
上构建它。详细信息:
然后,您可以使用 pg_class.oid :: regclass
自动转义和模式化表名。详细信息:
但这取决于您的要求和...味道的细节。
您必须使用 返回QUERY EXECUTE
返回每个行集。
EXECUTE
,然后是 RETURN NEXT
,完全不符合你期望的 。
您需要清理标识符。我在这里使用 quote_ident()
。或者你的查询将打破非标准的标识符,并允许SQL注入!
转换你的 col IN(子选择)
JOIN
。 这与使用一堆UNION查询
。它不会删除重复的行,并且实际上像 UNION ALL
。
就个人而言,我宁愿在系统目录
pg_class
上构建它。详细信息: 然后,您可以使用 pg_class.oid :: regclass
自动转义和模式化表名。详细信息:
但这取决于您的要求和...味道的细节。
I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria.
I could do this with a bunch of UNION
queries, but the number of tables I need to search can change over time, so I don't want to hard code it like that. I've been trying to develop a function that will loop through specific tables (they have a common naming convention) and return a table of records, but I'm not getting any results when I query the function. Function code is below:
CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$BODY$
DECLARE
formal_table text;
begin
FOR formal_table IN
select table_name from information_schema.tables
where table_schema = 'public' and table_name like 'formaltable%'
LOOP
EXECUTE 'SELECT natural_id, name, natural_id_numeric
FROM ' || formal_table ||
' WHERE natural_id_numeric IN (
select natural_id_numeric from internal_idlookup
where internal_id = ''7166571'')';
RETURN NEXT;
END LOOP;
Return;
END;
$BODY$
LANGUAGE plpgsql;
I am not getting any errors when I try to use the function, but it's not returning any rows:
SELECT * From internalid_formaltable_name_lookup();
Any idea where I went wrong?
CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$func$
DECLARE
formal_table text;
BEGIN
FOR formal_table IN
SELECT quote_ident(table_name)
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name LIKE 'formaltable%'
LOOP
RETURN QUERY EXECUTE
'SELECT t.natural_id, t.name, t.natural_id_numeric
FROM internal_idlookup i
JOIN public.' || formal_table || ' t USING (natural_id_numeric)
WHERE i.internal_id = 7166571'; -- assuming internal_id is numeric
END LOOP;
END
$func$ LANGUAGE plpgsql;
Major points:
You have to use
RETURN QUERY EXECUTE
to return each set of rows.
EXECUTE
, followed byRETURN NEXT
, does not do what you seem to expect at all.You need to sanitize identifiers. I am using
quote_ident()
here. Or your query will break with non-standard identifiers and allow SQL injection!Converted your
col IN (sub-select)
to a more efficientJOIN
.This is subtly different from using
a bunch of UNION queries
. It does not remove duplicate rows, and actually works likeUNION ALL
.
Personally, I'd rather build this on the system catalog pg_class
. Details:
Then you can work with the pg_class.oid::regclass
to escape and schema-qualify table names automatically. Details:
- Table name as a PostgreSQL function parameter
- Search across multiple tables and also display table name in resulting rows
But that depends on the details of your requirements and ... taste.
这篇关于函数循环并从多个表中选择数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!