在SQL结果集中将表名添加到每一列？ （特别是Postgres） [英] Prepend table name to each column in a result set in SQL? (Postgres specifically)

问题描述

如何获取结果集中各列的标签，以便在表的名称前加上名称？

How can I get the label of each column in a result set to prepend the name if its table?

我希望对单个表的查询都这样

I want this to happen for queries on single tables as well as joins.

示例：

  SELECT first_name, last_name FROM person;

我希望结果为：

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

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

I could use "AS" to define an alias for each column, but that would be tedious. I want this to happen automatically.

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

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

The reason for my question is that I am using a database driver that does not provide the meta-data informing me the database column from where the result set got its data. I am trying to write generic code to handle the result set.

我想知道如何在SQL中，或者至少在Postgres中做到这一点。

I would like to know how to do this in SQL generally, or at least in Postgres specifically.

SQLite具有这样的功能，尽管我现在莫名其妙地不赞成使用它。 SQLite具有两种编译指示设置：完整列名称& 短列名称。

SQLite had such a feature, though I see it is now inexplicably deprecated. SQLite has two pragma settings: full_column_names & short_column_names.

推荐答案

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

I know this question is a bit old, but perhaps someone will stumble over the answer and it will help them out.

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

The proper way to do what you're looking for is to create and use a View. Yes, it will be a bit tedious one-time to type out all those new column names as aliases, but if there are a lot of columns here's a trick you can use to leverage the PostgreSQL metadata to write out the text of the view:

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]

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

you can then copy and execute the results and voila:

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] 

这篇关于在SQL结果集中将表名添加到每一列？ （特别是Postgres）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！