Skip site navigation (1) Skip section navigation (2)

Re: functions returning sets

From: Alex du Plessis <alexdup01(at)telkomsa(dot)net>
To: Postgres novice mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: functions returning sets
Date: 2006-06-15 13:52:24
Message-ID: 44916618.6040601@telkomsa.net (view raw or flat)
Thread:
Lists: pgsql-novice

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

In response to

pgsql-novice by date

Next:From: kmh496Date: 2006-06-15 14:18:58
Subject: reference constraint creation problem
Previous:From: Michael FuhrDate: 2006-06-15 13:40:50
Subject: Re: functions returning sets

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group