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

Hello Andrew,

On 23-Jul-00 01:29:37, you wrote:

>> >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.

Yes.

>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 :-)

That's because you are commited to a single database. I am a Web
application developer, so database application portability matters to me
because my applications market is larger if do not depend on a particular
DBMS specific features.

>Are functions not available in other SQL dialects?

I don't know. I just don't want to rely on something that advanced for so
little use.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos(at)acm(dot)org
--
E-mail: mlemos(at)acm(dot)org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--