Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT * variant idea, is there something like this...

SELECT * variant idea, is there something like this...

From
"Matthew Smith"
Date:
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