Re: distinguishing identical columns after joins

>> But I still wonder it isn't a receiver/UI issue.  Does your reader know
>> the meaning of "a_" vs "b_" in a non-trivial self join?  Wouldn't you
>> rather have the output as a_col1, b_col1, a_col2, b_col2 ... for easy
>> comparison.  And who can make sense of a 100 column results set anyway?:)

Rob, I agree it's a doozy =) Regarding "a_" etc, I meant it as a
piece of some sort of underlying append/prepend operation for the
names in order to get the full names to display like you listed:
"a_col1" etc. As for my UI, I'm using PGAdmin since I haven't found
any other tools that let me retrieve and scroll over huge datasets as
easily as it does. Perhaps another UI might have some built-in
feature for this, but I haven't found it and I would still choose
PGAdmin for its ability to display huge datasets better.

> Off the cuff, a possible workaround would be to create multiple views of
> your table that rename the columns, i.e.
>
> CREATE VIEW vw_a_t1 AS
> SELECT
> col1 AS a_col1, col2 AS a_col2, …
> FROM
> t1;
>
> CREATE VIEW vw_b_t1 AS
> SELECT
> col1 AS b_col1, col2 AS b_col2, …
> FROM
> t1;
>
> Then you would do your select as
>
> SELECT *
> FROM vw_a_t1 JOIN vw_b_t1 ON a_col1 = b_col1;
>
> If you were often self-joining the table 3 or more times, you would
> obviously have to create views vw_c_t1, vw_d_t1, etc. If you need to do
> this for several tables, you might be able to create a function to
> create the views. The function would take a table name and the desired
> prefix as parameters and programatically construct, then EXECUTE the
> CREATE VIEW statement.
>
> --Lee
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center

Lee, I hadn't thought of this, and it's a great solution if I was
always doing the self-joins on the same table each time because it
plays out faster once the view-setup is out of the way. I'd only have
to reference the correct view for each part of the join. I didn't
specify, but I am looking for something more dynamic than this. To
use this effectively, I'd have to create a number of these views for
every one of my tables, which just feels like too much clutter for
something that feels like it should be simpler.

> In times like these, I usually write a query using
> information_schema.columns to generate the column list:
>
>
> SELECT ordinal_position,
> 1 AS table_instance,
> 'a.' || column_name || ' AS ' || column_name || '_a,'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'your_table_here'
> UNION ALL
> SELECT ordinal_position,
> 2 AS table_instance,
> 'b.' || column_name || ' AS ' || column_name || '_b,'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'your_table_here'
> ORDER BY table_instance,
> ordinal_position;
>
>
> Or something along those lines, and copy-and-paste the results into the
> query. It's quicker than typing them all out once you hit a certain number
> of columns, and certainly less typo-prone.
>
> It's not the shortcut you were thinking of but it works.

Stephen, I think this'll do the trick very nicely since it fits my
dynamic needs. I'll probably end up turning it into a PGAdmin macro
in order to make it even easier on myself. I dug in a bit on the
INFORMATION_SCHEMA and found that INFORMATION_SCHEMA.attributes will
help me use this same logic to build column lists when I'm working
with stored functions in lieu of tables. At least it'll work on those
I have defined to return composite data types. I don't suppose
there's a way to do this with functions that define multiple OUT
parameters in lieu of a custom composite type?

Thanks everyone!
sg