SQL 列名并将它们与 PostgreSQL 中另一个表中的行记录进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16379509/
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
SQL column names and comparing them to row records in another table in PostgreSQL
提问by b degnan
I have one of those cases where each SQL query works great, but not when I paste them together.
我有一种情况，每个 SQL 查询都可以很好地工作，但当我将它们粘贴在一起时就不行了。
I have two tables. One is dynamic that allows NULL values. The other helps fill the null values by providing a user with a questions. I take the column names from the dynamic table and see if I can get them to match with fields in the other questions table. Currently, we have this working PHP, but I have been trying put it all into our PostgreSQL database.
我有两张桌子。一种是允许 NULL 值的动态。另一个通过向用户提供问题来帮助填充空值。我从动态表中获取列名，看看是否可以让它们与其他问题表中的字段相匹配。目前，我们有这个可用的 PHP，但我一直在尝试将它全部放入我们的 PostgreSQL 数据库中。
This gets all of the column names from the dynamic table and return a comma separated list of strings.
这会从动态表中获取所有列名并返回一个逗号分隔的字符串列表。
select array_to_string(array_agg(quote_literal(column_name::text)),',') from
information_schema.columns where table_name='dynamic_table';
It works great, and returns something like: 'notificationemail','birthdate','lastnotificationcheck','createmethod'
它工作得很好，并返回类似：'notificationemail','birthdate','lastnotificationcheck','createmethod'
If I paste this list directly into my next query, it works and returns rows:
如果我将此列表直接粘贴到我的下一个查询中，它将起作用并返回行：
select * FROM questions_table WHERE questioncolumn IN
('notificationemail','birthdate','lastnotificationcheck','createmethod');
Now once I paste them together, I get something that runs but returns no rows:
现在，一旦我将它们粘贴在一起，我就会得到一些运行但不返回任何行的东西：
select * FROM questions_table WHERE questioncolumn IN
(select array_to_string(array_agg(quote_literal(column_name::text)),',') from
information_schema.columns where table_name='dynamic_table');
I am trying to determine how the static text field is different from the embedded SELECT inside the IN statement. It seems to make sense that it would work, but obviously, something is different from the dynamic list and the static text.
我试图确定静态文本字段与 IN 语句中嵌入的 SELECT 有何不同。它会起作用似乎是有道理的，但显然，与动态列表和静态文本有所不同。
EDIT: Thank you! I guess I was over thinking things. By not making it a string list and keeping it an object, it worked right away.
编辑：谢谢！我想我想多了。通过不使其成为字符串列表并将其保留为对象，它立即起作用。
采纳答案by sgeddes
I think this might be what you're looking for instead:
我认为这可能是你正在寻找的：
select *
FROM questions_table
WHERE questioncolumn IN
(
select column_name
from information_schema.columns where table_name='dynamic_table'
);
回答by Clodoaldo Neto
select *
FROM questions_table
WHERE questioncolumn IN (
select quote_literal(column_name::text)
from information_schema.columns
where table_name='dynamic_table'
);
回答by fredt
The IN clause expects a transient table, not a string of comma-separated values.
IN 子句需要一个临时表，而不是逗号分隔值的字符串。
select * FROM questions_table WHERE questioncolumn IN
select column_name from
information_schema.columns where table_name='dynamic_table';
