Thread

1. Subselects lack functionality

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-09-28T22:01:38Z

   Kuba Ober (winnie@hoth.amu.edu.pl) reports a bug with a severity of 3
   The lower the number the more severe it is.
   
   Short Description
   Subselects lack functionality
   
   Long Description
   1.
   Subselects don't allow one to use tuple set operators like UNION, INTERSECT, EXCEPT. It forces one to select the results into a temp table.
   2.
   It would be gr8 if single column subselects would allow themselves to be treated the same way as lists do as right-side arguments of IN operator.
   This would allow e.g. much more powerful DELETE statement, like
   DELETE FROM table WHERE id IN (SELECT ...)
   3.
   Subselects cannot be used as arguments to aggregation functions - I presume this is very weird and not expected at all, but I had such an idea.
   
   I don't know if it is expected behaviour (standards?), this should be explained in the docs (or am I searching in the wrong place?)
   
   
   Sample Code
   select (select id from table1 except select table1.id where expression) as id;
   
   (assuming that subselect would result just one tuple)
   
   No file was uploaded with this report
   
   
   

2. Re: Subselects lack functionality

   Lamar Owen <lamar.owen@wgcr.org> — 2000-09-28T22:20:52Z

   pgsql-bugs@postgresql.org wrote:
   > 2.
   > It would be gr8 if single column subselects would allow themselves to be treated the same way as lists do as right-side arguments of IN operator.
   > This would allow e.g. much more powerful DELETE statement, like
   > DELETE FROM table WHERE id IN (SELECT ...)
   
   This, AFAIK, works.  I am using just that construct in UPDATE -- I would
   assume DELETE would work... 
   
   Lessee...This works, as I just tested it on a client's database (inside
   a transaction, with a rollback at the end, of course).  You say
   something like
   
   DELETE from users WHERE uid IN (SELECT uid FROM users WHERE username ~*
   'testing') 
   
   to delete (of course, I know that that is a contrived example, but a
   working one).  
   
   More complex is 
   
   DELETE from personal_data WHERE uid IN (SELECT uid FROM users WHERE
   username ~* 'testing') 
   
   -- which works just fine on a client's database (again, inside a
   transaction so I could easily roll back the delete :-)).
   
   --
   Lamar Owen
   WGCR Internet Radio
   1 Peter 4:11
   
   

3. Re: Subselects lack functionality

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-09-29T04:11:33Z

   pgsql-bugs@postgresql.org writes:
   > Subselects don't allow one to use tuple set operators like UNION,
   > INTERSECT, EXCEPT.
   
   Yeah, we know.  The existing implementation of UNION etc. is a horrid
   kluge that only works at the top level of a SELECT (and not even very
   well there).  Fixing this will require a redesign of querytrees, which
   is currently planned for 7.2.
   
   > It would be gr8 if single column subselects would allow themselves to
   > be treated the same way as lists do as right-side arguments of IN
   > operator.
   
   As Lamar pointed out, this works now.
   
   > Subselects cannot be used as arguments to aggregation functions
   
   You have that backwards.  You don't do the select as an argument to
   the aggregate, you use it as a context for the aggregate, eg
   
   	 ... (SELECT max(foo) FROM bar WHERE baz) ...
   
   			regards, tom lane