SELECT * variant idea, is there something like this... - Mailing list pgsql-general
| From | Matthew Smith |
|---|---|
| Subject | SELECT * variant idea, is there something like this... |
| Date | |
| Msg-id | DEFEE2F80BA393408F883FE06F891238671FED@primary.corp.ausclad.com.au Whole thread Raw |
| List | pgsql-general |
Hi
Often I find myself working in a query where I need to select one or two fields from source table A
as renamed objects ( usually to prevent name duplication with other selected fields from another joined
source table B ) and then to also select all the remaining fields from source table A
I end up having to type out every field name from the underlying tables in the SELECT clause,
which is remedial and dull, and adds to code maintenance, as everytime I add a new field to the
table structure, I have to re-edit the queries as well.
In this situation I cant help but think it would be great to have something like the "all fields" * keyword token
as per the SELECT * sql command, that would add into the select field list any fields that have not been specifically referenced yet
eg SELECT **
or SELECT &
A visual example of what I mean
Table Fields
A job
A description
A amount
B job
B sub-item
B amount
The sql command
SELECT A.*, B.*
FROM A, B
WHERE A.Job = B.Job
results in all fields, but some have to be renamed to prevent duplicated names ( Job and Amount fields )
Job table A
Description table A
Amount table A
Job_1 table B
Sub_Item table B
Amount_1 table B
so I end up doing something like this
SELECT A.job, A.description, A.amount AS total_amount, B.*
FROM A, B
WHERE A.Job = B.Job
typing out all the field names from table A ( which can be a lot more than this simple example !!! )
this results in the following data struture ( field names )
Job
Description
Total_Amount ( manually renamed Table A's Amount field. To prevent clash with Table B's Amount field )
Job_1 ( Table B's Job field, auto renamed to prevent clash with earlier inclusion of Table A's Job field )
Sub_Item
Amount
but if I then add any new fields to Table A, the 2nd query will never pick them up, as the SELECT clause is restrictive in its coding ( I did not use the * token to get all fields )
What I see as being usefull is something like this
SELECT A.amount AS total_amount, A.** , B.*
FROM A, B
WHERE A.Job = B.Job
this results in the following data struture ( field names )
Total_Amount ( manually renamed Table A's Amount field. To prevent clash with Table B's Amount field )
Job auto included by the A.** in the select clause Description auto included by the A.** in the select clause
Job_1 ( Table B's Job field, auto renamed to prevent clash with earlier inclusion of Table A's Job field )
Sub_Item
Amount
Note Table A field Amount is not added in automatically by the "Select A.**" code as this field has already been referenced manually by
the "Select A.amount AS total_amount" code
Does something like this already exist,
is there a way around the problem
or would this make a useful additional feature ?
Thanks
Matthew Smith
pgsql-general by date: