pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Returning sets from functions?
3+ messages / 2 participants
[nested] [flat]

* Returning sets from functions?
@ 2000-04-23 14:15 mig@utdt.edu
  0 siblings, 0 replies; 3+ messages in thread

From: mig@utdt.edu @ 2000-04-23 14:15 UTC (permalink / raw)
  To: pgsql-sql

Consider
    create function followers(text) returns setof text as 
          'select id from ids where id > $1 '
          language 'sql';

The function seems to work OK:
    select followers('h');
does return all indexes after the index 'h'.

On the other hand,
    select * from ids where id in (select followers('h'));
produces the message
  ERROR:  An operand to the '=' operator returns a set of text,
        but '=' takes single values, not sets.

Apparently the phrase
    select followers('h');
returns a set with members which are 'setof text'. psql has no trouble
displaying all rows, but the parser does ...

So, my question is: can I produce a set in a function so that it
mimicks perfectly the return set of a select query?

I am using PG 6.5.3; is this already solved in the 7.0 beta?

Thanks

Miguel Sofer




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Returning sets from functions?
@ 2000-04-23 15:02 mig@utdt.edu
  2000-04-23 21:32 ` Re: Re: Returning sets from functions? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: mig@utdt.edu @ 2000-04-23 15:02 UTC (permalink / raw)
  To: pgsql-sql

Sorry, I forgot part of my question in the previous message:
 
  How do you return a "setof" value from a PG/Tcl procedure? Returning
  a list does not seem to work ...

### PREVIOUS MESSAGE WAS ...

Consider
    create function followers(text) returns setof text as 
          'select id from ids where id > $1 '
          language 'sql';

The function seems to work OK:
    select followers('h');
does return all indexes after the index 'h'.

On the other hand,
    select * from ids where id in (select followers('h'));
produces the message
  ERROR:  An operand to the '=' operator returns a set of text,
        but '=' takes single values, not sets.

Apparently the phrase
    select followers('h');
returns a set with members which are 'setof text'. psql has no trouble
displaying all rows, but the parser does ...

So, my question is: can I produce a set in a function so that it
mimicks perfectly the return set of a select query?

I am using PG 6.5.3; is this already solved in the 7.0 beta?

Thanks

Miguel Sofer




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Re: Returning sets from functions?
  2000-04-23 15:02 Re: Returning sets from functions? mig@utdt.edu
@ 2000-04-23 21:32 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2000-04-23 21:32 UTC (permalink / raw)
  To: mig@utdt.edu; +Cc: pgsql-sql

mig@utdt.edu writes:
> Consider
>     create function followers(text) returns setof text as 
>           'select id from ids where id > $1 '
>           language 'sql';

> The function seems to work OK:
>     select followers('h');
> does return all indexes after the index 'h'.

> On the other hand,
>     select * from ids where id in (select followers('h'));
> produces the message
>   ERROR:  An operand to the '=' operator returns a set of text,
>         but '=' takes single values, not sets.

Seems to work in 7.0, which actually surprises me quite a bit ;-).
Functions returning sets are only very shakily implemented right now.
We've cured a few bugs since 6.5, but it's going to be difficult
to make much further progress with them until the fabled querytree
redesign happens (which will also bring outer joins and some other
good stuff).  Current plans are to tackle that for 7.2, which might
be out by the end of the year or so.

> Sorry, I forgot part of my question in the previous message:
>   How do you return a "setof" value from a PG/Tcl procedure?

Right now I think you can't ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-04-23 21:32 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-23 14:15 Returning sets from functions? mig@utdt.edu
2000-04-23 15:02 Re: Returning sets from functions? mig@utdt.edu
2000-04-23 21:32 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox