Thread: [Fwd: Re: functions returning sets]

-------- Original Message --------
Subject: Re: [NOVICE] functions returning sets
Date: Thu, 15 Jun 2006 15:12:51 +0200
From: Alex du Plessis <alexdup01@telkomsa.net>
Reply-To: alexdup01@telkomsa.net
Organization: Phenomenal Consulting CC
To: Michael Fuhr <mike@fuhr.org>
References: <449157D4.1090304@telkomsa.net>
<20060615130013.GA3866@winnie.fuhr.org>

It comes from the help in pgAdminIII:

Section 32.4.5 SQL Functions Returning sets.

This is paragraph +-3

"Currently, functions returning sets may also be called in the select
list of a query. For each row that the query generates by itself, the
function returning set is invoked, and an output row is generated for
each element of the function's result set. Note, however, that this
capability is deprecated and may be removed in future releases. The
following is an example function returning a set from the select list: "

I dearly need to set up a function with my selection criteria and then
run a slightly complex query because my client does not want to parse
the SQL properly and causes the application to crash.  A function
returning a set of rows would solve the problem elegantly, but I do not
want to use something that will disappear suddenly. Now is the time to
look for alternatives (If I read the paragraph correctly)

Michael Fuhr wrote:
> On Thu, Jun 15, 2006 at 02:51:32PM +0200, Alex du Plessis wrote:
>> From the pqsql help it seems that functions returning sets are
>> deprecated and might even be removed in spme future release.
>
> What exactly did you read and where did you read it?
>

On Thu, Jun 15, 2006 at 03:18:05PM +0200, Alex du Plessis wrote:
> Section 32.4.5 SQL Functions Returning sets.
>
> This is paragraph +-3
>
> "Currently, functions returning sets may also be called in the select
> list of a query. For each row that the query generates by itself, the
> function returning set is invoked, and an output row is generated for
> each element of the function's result set. Note, however, that this
> capability is deprecated and may be removed in future releases. The
> following is an example function returning a set from the select list: "

This paragraph refers to using the set-returning function in the
select list instead of the FROM list.  Do you need that functionality,
or can you use "SELECT * FROM function_name(args)" instead?

> I dearly need to set up a function with my selection criteria and then
> run a slightly complex query because my client does not want to parse
> the SQL properly and causes the application to crash.

What do you mean by "parse the SQL properly"?  Do you mean your
client doesn't want to embed a complex query in their application
code?  Or something else?  It's not clear what they'd be doing that
would cause the application to crash.

> A function returning a set of rows would solve the problem elegantly,
> but I do not want to use something that will disappear suddenly. Now
> is the time to look for alternatives (If I read the paragraph correctly)

Do you really need a function, or would writing the complex query
as a view work?  In any case, I don't think that set-returning
functions themselves are deprecated, but just a particular way of
calling an SQL-language set-returning function.

--
Michael Fuhr

Michael Fuhr wrote:
> On Thu, Jun 15, 2006 at 03:18:05PM +0200, Alex du Plessis wrote:
>> Section 32.4.5 SQL Functions Returning sets.
>>
>> This is paragraph +-3
>>
>> "Currently, functions returning sets may also be called in the select
>> list of a query. For each row that the query generates by itself, the
>> function returning set is invoked, and an output row is generated for
>> each element of the function's result set. Note, however, that this
>> capability is deprecated and may be removed in future releases. The
>> following is an example function returning a set from the select list: "
>
> This paragraph refers to using the set-returning function in the
> select list instead of the FROM list.  Do you need that functionality,
> or can you use "SELECT * FROM function_name(args)" instead?

I can certainly use the "SELECT * FROM " construct
>
>> I dearly need to set up a function with my selection criteria and then
>> run a slightly complex query because my client does not want to parse
>> the SQL properly and causes the application to crash.
>
> What do you mean by "parse the SQL properly"?  Do you mean your
> client doesn't want to embed a complex query in their application
> code?  Or something else?  It's not clear what they'd be doing that
> would cause the application to crash.
>

My sql looks like this:

SELECT a.id,b.longname FROM ((SELECT id FROM a) EXCEPT (SELECT b.ridsob
FROM b WHERE b.riduser = 12)) AS a LEFT JOIN setofbooks AS b ON a.id = b.id'

And  my client is written in FreePascal and Lazarus as IDE.

The error returned:  Database Error:  Parse error at or near "AS" (and
it seems to be having a problem with the second AS - not the first)
>> A function returning a set of rows would solve the problem elegantly,
>> but I do not want to use something that will disappear suddenly. Now
>> is the time to look for alternatives (If I read the paragraph correctly)
>
> Do you really need a function, or would writing the complex query
> as a view work?  In any case, I don't think that set-returning
> functions themselves are deprecated, but just a particular way of
> calling an SQL-language set-returning function.
>
The function helps me to pass the id parameter into the query, meaning
that I do not use the client to process the query