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

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 betweenprimary & 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