在 SQL 结果集中的每一列前面添加表名？ （特别是 Postgres）

Question

如何获取结果集中每列的标签以在其表前面添加名称？

我希望对单个表和联接的查询发生这种情况。

示例：

  SELECT first_name, last_name FROM person;

我希望结果是：

 | person.first_name | person.last_name |
 |-------------------|------------------|
 | Wendy             | Melvoin          |
 | Lisa              | Coleman          |

我可以使用“AS”为每列定义一个别名，但这会很乏味。我希望这一切自动发生。

  SELECT first_name AS person.first_name, last_name AS person.last_name FROM person;

我的问题的原因是我使用的数据库驱动程序不提供元数据来通知我结果集从中获取数据的数据库列。我正在尝试编写通用代码来处理结果集。

我想知道一般如何在 SQL 中执行此操作，或者至少在 Postgres 中具体执行此操作。

SQLite 有这样的功能，尽管我发现它现在已莫名其妙地被弃用了。 SQLite 有两个 pragma 设置： full_column_names & short_column_names。

Answer 1

我知道这个问题有点老了，但也许有人会偶然发现答案，这会对他们有所帮助。

执行您要查找的操作的正确方法是创建并使用视图。是的，一次性输入所有这些新列名作为别名会有点乏味，但如果有很多列，您可以使用一个技巧来利用 PostgreSQL 元数据写出视图的文本：

select 'CREATE OR REPLACE VIEW people AS SELECT ' || 
(select string_agg(column_name || ' AS person_' || column_name, ', ')
from information_schema.columns
where table_name = 'person'
group by table_name) || 
' FROM person;';

运行此命令会产生：

?column?                                                 
------------------------------------------------------------------------------------------------------------- 
CREATE OR REPLACE VIEW people AS SELECT last_name AS person_last_name, first_name AS person_first_name FROM person; 

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 4/21/12 2:05:21 PM EDT ] [Execution: 9/ms]

然后您可以复制并执行结果，瞧：

select * from people;

 person_last_name     person_first_name    
 -------------------  -------------------- 
 Melvoin              Wendy                
 Coleman              Lisa                 

 2 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms] 

Answer 2

要在单个语句中获取VIEW（Daryl 的想法）< /strong> 使用函数或 < code>DO 命令 与 EXECUTE：

DO
$do$
BEGIN

EXECUTE (
   SELECT format(
      'CREATE TEMP VIEW people AS SELECT %s FROM %I'
     , string_agg(format('%I AS %I', attname, attrelid::regclass || '.' || attname), ', ')
     , attrelid::regclass)
   FROM   pg_attribute
   WHERE  attrelid = 'person'::regclass  -- supply source table name once
   AND    attnum > 0
   AND    NOT attisdropped
   GROUP  BY attrelid
   );

END
$do$;

这会立即执行以下形式的命令：

CREATE OR REPLACE VIEW people AS
SELECT person_id AS "person.person_id"
     , first_name AS "person.first_name"
     , last_name AS "person.last_name"
FROM   person;

将合法的列名称与 '_' 连接起来会更方便，而不是用 '_' 连接'.'。但无论如何，您都需要为需要双引号的非标准名称做好准备（并防止可能的 SQL 注入）。

您可以选择提供架构限定的表名称 (myschema.person)。如果模式名称位于当前 搜索路径。

为了重复使用，您可以将其包装到 plpgsql 函数中，并将表名称作为 text 参数。所有文本到代码的转换都在这里进行清理，以防止 SQL 注入。此处包含更多信息的示例：

• 表名称作为 PostgreSQL 函数参数< /a>

并且您可以在 Postgres 9.4+ 中使用新的 to_regclass()：

• 如何检查给定架构中是否存在表

Answer 3

“如何获取结果集中每列的标签以在其表中添加名称？”

正如其他答案所示，您不能直接这样做。但是，您可以为共享相同名称的列（如外键和last_updated 列）添加表名。这将为您提供足够的信息来判断该列来自哪个表。

select t1.*, t2.*, t3.*
from table1 t1
join table2 t2 using (fk1_id)
join table3 t3 using (fk2_id)

列名称将显示如下：

pk1_id | t1.last_updated | unique_col_name_from_t1 | t1.fk1_id | t1.fk2_id | t2.fk1_id | t2.last_updated | unique_col_name_from_t2 | t3.fk2_id | t3.last_updated | unique_col_name_from_t3

这对您的特定场景没有帮助，但回答了发布的一般问题。