On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent <robjsarg...@gmail.com> wrote: > > > On 03/01/2011 12:47 PM, S G wrote: >> This question is particularly geared towards self-joins, but can apply >> to any join where the tables involved have any identical column names. >> Aside from explicit column references, is there any way to pull all >> columns (*) from each table in a join and quickly append/prepend some >> identifier to distinguish them from each other? For example, table t1 >> contains columns named col1 and col2: >> >> SELECT >> * >> FROM >> t1 AS a >> INNER JOIN >> t1 AS b >> ON >> a.col1 = b.col1 >> >> would yield a result set with column names: col1, col2, col1, col2. >> I'm looking for something that would automatically rename the columns >> like: a_col1, a_col2, b_col1, b_col2. Does such functionality exist? >> It's not such a big deal in this example, but it can be quite tedious >> to explicitly reference and rename every single column for such joins >> when the tables involved have a very large number of columns. >> >> I would beg for the same functionality when expanding compound >> datatypes. For example, a compound datatype cd1 exists with fields >> named f1 and f2: >> >> SELECT >> ((value1, value2)::cd1).* AS a >> >> normally produces a result set with column names: f1, f2. I'm looking >> for something that would produce column names: a_f1, a_f2. >> >> Thanks! >> sg >> > select a.col1 as a_col1 etc doesn't do it for you? > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Rob, what you wrote certainly does work. But suppose you had to do that for a join with 50 columns in each table, and you really needed to see all those columns show up in the final result set, and furthermore, you needed to be able to identify each one uniquely in the final result set. Explicit renaming works, but it's tedious. Call me lazy. I'm hoping a column-renaming shortcut exists that works with the "SELECT *" concept. If such a shortcut doesn't exist, I believe it easily could exist utilizing the following syntax: SELECT (a).* AS a_, (b).* AS b_ FROM t1 AS a INNER JOIN t1 AS b ON a.col1 = b.col1 which currently discards the AS identifiers and defaults to the column names as identified in their respective tables. Though implementing this is another issue altogether... I'm just asking if such a shortcut already exists. Thanks! sg -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
