问题描述
我需要从两个或更多表("A","B")中选择行.它们具有差异列,我不使用继承.
I need to select rows from two and more tables ("A", "B"). They have differences columns and I don't use inheritance for it.
所以.例如:
SELECT * FROM "A" UNION SELECT * FROM "B"
ERROR: each UNION query must have the same number of columns
我能理解为什么.
我尝试从根表的根架构中获取相交的列:
I try get intersected columns from root schema in root table:
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'client_root' AND table_name ='conditions'
没关系!但我不使用查询:
It's ok! But I don't use query:
SELECT
(SELECT column_name FROM information_schema.columns
WHERE table_schema = 'client_root' AND table_name ='conditions')
FROM "client_123"."A"
所以.如何将子选择数据放入根选择中?
So. How I can put sub select data in root select?
推荐答案
您试图做的事情几乎不可能全部实现.
What you are trying to do is hardly possible in its entirety.
首先,这是您可以做的所要做的:一个为此类查询创建SQL的plpgsql函数:
First, here is what you can do: a plpgsql function that creates the SQL for such a query:
CREATE OR REPLACE FUNCTION f_union_common_col_sql(text, text)
RETURNS text
AS $function$
DECLARE
_cols text;
BEGIN
_cols := string_agg(attname, ', ')
FROM (
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = $1::regclass::oid
AND a.attnum >= 1
INTERSECT
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = $2::regclass::oid
AND a.attnum >= 1
) x;
RETURN 'SELECT ' || _cols || '
FROM ' || quote_ident($1) || '
UNION
SELECT ' || _cols || '
FROM ' || quote_ident($1);
END;
$function$ LANGUAGE plpgsql;
COMMENT ON FUNCTION f_union_common_col_sql(text, text) IS 'Create SQL to query all visible columns that two tables have in common.
# Without duplicates. Use UNION ALL if you want to include duplicates.
# Depends on visibility dicatated by search_path
$1 .. table1: optionally schema-qualified, case sensitive!
$2 .. table2: optionally schema-qualified, case sensitive!';
致电:
SELECT f_union_common_col_sql('myschema1.tbl1', 'myschema2.tbl2');
为您提供完整的查询.在第二个调用中执行它.
Gives you the complete query. Execute it in a second call.
您可以在 plpgsql函数手册中找到我在此处使用的大多数内容.
PostgreSQL 9.0引入了集合函数string_agg()
.在旧版本中,您将:array_to_string(array_agg(attname), ', ')
.
You can find most everything I used here in the manual on plpgsql functions.
The aggregate function string_agg()
was introduced with PostgreSQL 9.0. In older versions you would: array_to_string(array_agg(attname), ', ')
.
接下来,这是您几乎无法做到的:
CREATE OR REPLACE FUNCTION f_union_common_col(text, text)
RETURNS SETOF record AS
$BODY$
DECLARE
_cols text;
BEGIN
_cols := string_agg(attname, ', ')
FROM (
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = $1::regclass::oid
AND a.attnum >= 1
INTERSECT
SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = $2::regclass::oid
AND a.attnum >= 1
) x;
RETURN QUERY EXECUTE '
SELECT ' || _cols || '
FROM quote_ident($1)
UNION
SELECT ' || _cols || '
FROM quote_ident($2)';
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
COMMENT ON FUNCTION f_union_common_col(text, text) IS 'Query all visible columns that two tables have in common.
# Without duplicates. Use UNION ALL if you want to include duplicates.
# Depends on visibility dicatated by search_path
# !BUT! you need to specify a column definition list for every call. So, hardly useful.
$1 .. table1 (optionally schema-qualified)
$2 .. table1 (optionally schema-qualified)';
函数调用要求您指定目标列的列表.因此,这一点都没有用:
A function call requires you to specify the list of target columns. so this is hardly useful at all:
SELECT * from f_union_common_col('myschema1.tbl1', 'myschema2.tbl2')
ERROR: a column definition list is required for functions returning "record"
没有简单的方法可以解决此问题.您将必须动态创建一个函数或至少一个复杂类型.这是我停下来的地方.
There is no easy way around this. You would have to dynamically create a function or at least a complex type. This is where I stop.
这篇关于在PostgreSQL中选择不带函数的动态列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!