>> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
