Re: Can't put sub-queries values in queries results?

Manuel Lemos wrote:
>
> >>
> >> SELECT f1, (SELECT COUNT(*) FROM t2 WHERE t2.f2=t1.f1) AS my_count FROM t1
> >>
> >> PostgreSQL does not seem to understand this.  I wonder if this is a
> >> limitation or I am doing something wrong.
> >>
> >> If I can't do what I want this way, I wonder if is there some other way to
> >> do it besides making two queries by passing the values from one to the
> >> other.
>
> >What about defining a function for the sub-query:
>
> >CREATE FUNCTION count_subs( INT4 ) RETURNS INT4 AS '
> >SELECT COUNT(*) FROM t2 WHERE t2.f2=$1;
> >' LANGUAGE 'SQL';
>
> >Then you should be able to:
>
> >SELECT f1, count_subs(f1) FROM t1;
>
> That seems to work, thank you, but I can't rely on things that are specific
> of PostgreSQL because I need it to work on databases with functions.
>
> Anyway, I wonder why PostgreSQL accepts this syntax but could not accept
> sub-queries as column value expression.
>
> Any other ideas?

You can possibly do a join between t1 and t2 and UNION that with the set
of records which don't join with a '0' in the count column.  A lot more
work.

Personally I don't get bogged down tying to be database agnostic - I use
PostgreSQL extensions when they're useful because I figure I can do that
with something that is BSD or GPL in ways that I wouldn't dream of tying
myself to a commercial product.  Also, most of my experience with
databases is with non-SQL ones, where extensions are just the whole 4GL
/ query language :-)

Are functions not available in other SQL dialects?

Cheers,
                    Andrew.
--
_____________________________________________________________________
            Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267