Re: functions returning sets

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