SQL SELECT 语句 postgres 中的动态列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2374660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic column in SELECT statement postgres
提问by Sujit
I am quite new to the postgresql.
我对 postgresql 很陌生。
what is the best way to achieve this?
实现这一目标的最佳方法是什么？
SELECT get_columns()
FROM table_name;
get_columns()will provide the column names for the query. I saw people advising to use EXECUTE statement but I couldn't got that working.
get_columns()将为查询提供列名。我看到有人建议使用 EXECUTE 语句，但我无法做到这一点。
Lets say there is table Test with columns a,b,c and I want to run
假设有包含 a、b、c 列的表 Test，我想运行
SELECT a,b FROM Test;
SELECT a,c FROM Test;
with column names generated dynamically.
列名是动态生成的。
回答by Sujit
In order to write a dynamic query you would have to do something like:
为了编写动态查询，您必须执行以下操作：
EXECUTE 'SELECT '|| get_columns()|| ' FROM table_name' INTO results
Please read the documentation: http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html
请阅读文档：http: //developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html
回答by Micha? Niklas
In such case I would use PL/pgSQL function using cursor.
在这种情况下，我会使用PL/pgSQL 函数使用 cursor。
回答by MkV
Since you are using COPY FROM to a known large table, CREATE a FUNCTION which returns SETOF bigtable and SELECTs all the columns from the specific type, use NULL AS fieldname for the fields which are not required in that specific case, something like:
由于您使用 COPY FROM 到一个已知的大表，创建一个返回 SETOF bigtable 并从特定类型中选择所有列的 FUNCTION，对于在该特定情况下不需要的字段使用 NULL AS 字段名，例如：
# \d SMALL
Table "public.small"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
d | integer |
# \d LARGE
Table "public.large"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
d | integer |
# CREATE OR REPLACE FUNCTION myData()
RETURNS SETOF large LANGUAGE SQL AS $$
SELECT a,
CASE WHEN a = 1
THEN b
ELSE
NULL
END as b,
CASE WHEN a = 2
THEN c
ELSE
NULL
END AS c,
d
FROM small;
$$;
# SELECT * FROM mydata();
# COPY (SELECT * FROM myData()) TO STDOUT;
Obviously SQL might not be the best language to use, so PL/PgSQL or PL/Perl (or whatever) may be appropriate.
显然 SQL 可能不是最好的语言，所以 PL/PgSQL 或 PL/Perl（或其他）可能是合适的。
回答by Scott Bailey
You wont be able to use a function to generate a column list. And I really don't think this is the best way to approach the problem... That said, you can do it with 8.4 like so:
您将无法使用函数来生成列列表。而且我真的不认为这是解决问题的最佳方法......也就是说，您可以像这样使用 8.4 来做到这一点：
CREATE OR REPLACE FUNCTION dyn(p_name VARCHAR)
RETURNS SETOF RECORD AS
$$
DECLARE
p_sql TEXT;
BEGIN
SELECT 'SELECT ' ||
CASE p_name WHEN 'foo' THEN ' col1, col2, col3, col4 '
WHEN 'bar' THEN 'col3, col4, col5, col6'
WHEN 'baz' THEN 'col1, col3, col4, col6' END ||
' FROM mytest'
INTO p_sql;
RETURN QUERY EXECUTE p_sql;
END
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION dyn(p_name VARCHAR)
RETURNS SETOF RECORD AS
$$
DECLARE
p_sql TEXT;
BEGIN
SELECT 'SELECT ' ||
CASE p_name WHEN 'foo' THEN ' col1, col2, col3, col4 '
WHEN 'bar' THEN 'col3, col4, col5, col6'
WHEN 'baz' THEN 'col1, col3, col4, col6' END ||
' FROM mytest'
INTO p_sql;
RETURN QUERY EXECUTE p_sql;
END
$$ LANGUAGE 'plpgsql';
Usage would be: SELECT * FROM dyn('foo') AS (one int, two int, three int, four int)
用法是：SELECT * FROM dyn('foo') AS（一个整数，两个整数，三个整数，四个整数）
But honestly I'd suggest just making a view for each device.
但老实说，我建议只为每个设备做一个视图。
回答by Chris Travers
I think your biggest problem is that you need to return rows in a way that PostgreSQL can understand. This means basically, you can return a refcursor or you can return a consistent set of data types. I prefer the latter because it makes the system a bit more consistent from a programming perspective and there are some advanced directions you can take that but I can see the other way too.
我认为您最大的问题是您需要以 PostgreSQL 可以理解的方式返回行。这意味着基本上，您可以返回一个 refcursor，也可以返回一组一致的数据类型。我更喜欢后者，因为从编程的角度来看，它使系统更加一致，并且您可以采用一些高级方向，但我也可以看到另一种方式。
回答by Frank Heikens
This is how you get the columnnames in a table:
这是获取表中列名的方式：
SELECT
column_name
FROM
information_schema.columns
WHERE
table_name = 'test';
