Re: How get column-wise table info from an arbitrary query?

[Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>]

On 31/07/13 09:57, David Johnston wrote:

Gavin Flower-2 wrote

I use 'id' for the primary key, and [tablename]_id for each foreign key, 
I always qualify my column references in SQL, but I would never use 
SELECT * when selecting from more than one table.


Cheers,
Gavin

On 30/07/13 21:41, sidthegeek wrote:

I really dislike ambiguous column names across tables in a database. Use
the
convention [tablename]_id for each id so every column name is self
describing. That way you can:

select * from providers inner join provider_types
using(provider_type_id);

No need for table aliases, column aliases and no ambiguity.

I'm in the "prefix the id column" camp.  I do not use "ORM" middle-ware so
that may be a reason I do not have any difficulties but one of the big
advantages to table-prefixing generic column names is that you can then make
the assumption that any two columns with the same name represent the same
data.  It does make "SELECT *" more useful when running interactive queries
and, more importantly, it makes using NATURAL JOIN and USING (...) much
easier - and I hate using ON (...) to perform a join (and I never use the
"FROM a, b WHERE a = b" cartesian join construct).

To the original question introspection of dynamic SQL is not a strong point
of PostgreSQL (cannot speak to other products).  Given the nature of how a
query works and the fact that columns can be created on-the-fly (i.e., not
belonging to any schema) this is not surprising.  You could try running and
capturing the output of EXPLAIN with various options like JSON and VERBOSE
and store that - it depends on your use-case.

David J.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-get-column-wise-table-info-from-an-arbitrary-query-tp5735090p5765675.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hmm...

I adopted the convention of just using 'id' for a table's primary key so I could easily distinguish between primary & foreign keys, this was before I came across "ORM" middle-ware. Also, since I know what table I'm looking at, it seemed redundant to also specify the table name as part of the table's primary key!

I've used dynamic SQL extensively in SyBase, but not yet needed to in Postgres - not that Postgres is 'better' in this regard, just didn't have the use case.


Cheers,
Gavin