Postgres Pro
Downloads
Home   >   mailing lists

Thread: PL/pgSQL and SETOF

PL/pgSQL and SETOF

From
Cultural Sublimation
Date:
Hi,

I am having trouble getting a really simple PL/pgSQL function to work.
I am beginning to wonder if there is not a bug somewhere, or if Postgresql's
type system is not broken.  Anyway, suppose I have the following table and
type defined:

    CREATE TABLE items
        (
        item_id        int,
        item_name    text,
        item_etc    text
        );

    CREATE TYPE simple_item_t AS
        (
        item_id        int,
        item_name    text
        );


It's easy to create a SQL function that returns a set of simple items:

    CREATE FUNCTION get_items ()
    RETURNS SETOF simple_item_t
    LANGUAGE sql STABLE AS
    $$
        SELECT item_id, item_name FROM items;
    $$;


Now, all I want is to create the equivalent PL/pgSQL function.  Nothing
more, nothing less.  This is the simplest version I can come up with:

    CREATE FUNCTION get_items2 ()
    RETURNS SETOF simple_item_t
    LANGUAGE plpgsql STABLE AS
    $$
    DECLARE
        item    simple_item_t%ROWTYPE;
    BEGIN
        FOR item IN SELECT item_id, item_name FROM items
            LOOP
                RETURN NEXT item;
            END LOOP;
        RETURN;
    END
    $$;


Unfortunately it doesn't work!  Postgresql complains that "set-valued
function called in context that cannot accept a set".  Anyway, what am
I doing wrong, and what is the simplest way of translating get_items
in PL/pgSQL?

Thanks in advance!
C.S.



      ____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs

Re: PL/pgSQL and SETOF

From
Sam Mason
Date:
On Fri, Nov 30, 2007 at 09:09:28AM -0800, Cultural Sublimation wrote:
> Postgresql complains that "set-valued
> function called in context that cannot accept a set".  Anyway, what am
> I doing wrong, and what is the simplest way of translating get_items
> in PL/pgSQL?

I would guess you're calling it like:

  SELECT get_items2();

whereas, you should call set returning functions like:

  SELECT * FROM get_items2();


  Sam

Re: PL/pgSQL and SETOF

From
"Pavel Stehule"
Date:
Hello


> Unfortunately it doesn't work!  Postgresql complains that "set-valued
> function called in context that cannot accept a set".  Anyway, what am
> I doing wrong, and what is the simplest way of translating get_items
> in PL/pgSQL?
>
> Thanks in advance!
> C.S.

try

select * from get_items2()

Regards
Pavel Stehule
>
>
>
>       ____________________________________________________________________________________
> Get easy, one-click access to your favorites.
> Make Yahoo! your homepage.
> http://www.yahoo.com/r/hs
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: PL/pgSQL and SETOF

From
Cultural Sublimation
Date:
> I would guess you're calling it like:
>
>   SELECT get_items2();
>
> whereas, you should call set returning functions like:
>
>   SELECT * FROM get_items2();

Hi,

Yeah, that was the problem -- thanks!

But still on that subject: is my version of get_items2 the simplest
that is possible in PL/pgSQL?  It seems awfully verbose compared to
the SQL version...

Regards,
C.S.



      ____________________________________________________________________________________
Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs

Re: PL/pgSQL and SETOF

From
"Pavel Stehule"
Date:
On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
> > I would guess you're calling it like:
> >
> >   SELECT get_items2();
> >
> > whereas, you should call set returning functions like:
> >
> >   SELECT * FROM get_items2();
>
> Hi,
>
> Yeah, that was the problem -- thanks!
>
> But still on that subject: is my version of get_items2 the simplest
> that is possible in PL/pgSQL?  It seems awfully verbose compared to
> the SQL version...
>

no. Your simplest version is historic relict and is available only in
sql language. I am not sure, maybe in C language too.

Pavel



> Regards,
> C.S.
>
>
>
>       ____________________________________________________________________________________
> Never miss a thing.  Make Yahoo your home page.
> http://www.yahoo.com/r/hs
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: PL/pgSQL and SETOF

From
Alvaro Herrera
Date:
Pavel Stehule escribió:
> On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:

> > But still on that subject: is my version of get_items2 the simplest
> > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > the SQL version...
>
> no. Your simplest version is historic relict and is available only in
> sql language. I am not sure, maybe in C language too.

But isn't the case that on 8.3 you would be able to rewrite it more
easily using RETURN QUERY?

--
Alvaro Herrera                          Developer, http://www.PostgreSQL.org/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)

Re: PL/pgSQL and SETOF

From
"Pavel Stehule"
Date:
On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Pavel Stehule escribió:
> > On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
>
> > > But still on that subject: is my version of get_items2 the simplest
> > > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > > the SQL version...
> >
> > no. Your simplest version is historic relict and is available only in
> > sql language. I am not sure, maybe in C language too.
>
> But isn't the case that on 8.3 you would be able to rewrite it more
> easily using RETURN QUERY?
>

yes. sure. But it is different question than call SRF function in
normal context.

Pavel


> --
> Alvaro Herrera                          Developer, http://www.PostgreSQL.org/
> "Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
>

Re: PL/pgSQL and SETOF

From
Alvaro Herrera
Date:
Pavel Stehule escribió:
> On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > Pavel Stehule escribió:
> > > On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
> >
> > > > But still on that subject: is my version of get_items2 the simplest
> > > > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > > > the SQL version...
> > >
> > > no. Your simplest version is historic relict and is available only in
> > > sql language. I am not sure, maybe in C language too.
> >
> > But isn't the case that on 8.3 you would be able to rewrite it more
> > easily using RETURN QUERY?
>
> yes. sure. But it is different question than call SRF function in
> normal context.

Hmm, you are misunderstanding Cultural or I am.  I think his verbosity
comment is not about the way the function is called, but rather about
the function body itself.

--
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers

Re: PL/pgSQL and SETOF

From
"Pavel Stehule"
Date:
On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Pavel Stehule escribió:
> > On 30/11/2007, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > > Pavel Stehule escribió:
> > > > On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
> > >
> > > > > But still on that subject: is my version of get_items2 the simplest
> > > > > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > > > > the SQL version...
> > > >
> > > > no. Your simplest version is historic relict and is available only in
> > > > sql language. I am not sure, maybe in C language too.
> > >
> > > But isn't the case that on 8.3 you would be able to rewrite it more
> > > easily using RETURN QUERY?
> >
> > yes. sure. But it is different question than call SRF function in
> > normal context.
>
> Hmm, you are misunderstanding Cultural or I am.  I think his verbosity
> comment is not about the way the function is called, but rather about
> the function body itself.
>

It's my English. I am sorry. You have true.

Thank you for correction

Pavel

> --
> Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
> "Before you were born your parents weren't as boring as they are now. They
> got that way paying your bills, cleaning up your room and listening to you
> tell them how idealistic you are."  -- Charles J. Sykes' advice to teenagers
>

Re: PL/pgSQL and SETOF

From
"Merlin Moncure"
Date:
On Nov 30, 2007 1:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> On 30/11/2007, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:
> > But still on that subject: is my version of get_items2 the simplest
> > that is possible in PL/pgSQL?  It seems awfully verbose compared to
> > the SQL version...
> >
>
> no. Your simplest version is historic relict and is available only in
> sql language. I am not sure, maybe in C language too.

It is extremely useful to be able call functions in this way.  I
really wish it were possible to do this with pl/sql functions as
well...

merlin

Re: PL/pgSQL and SETOF

From
"Pavel Stehule"
Date:
Hello

> >
> > no. Your simplest version is historic relict and is available only in
> > sql language. I am not sure, maybe in C language too.
>
> It is extremely useful to be able call functions in this way.  I
> really wish it were possible to do this with pl/sql functions as
> well...
>
> merlin
>

where is this form better, than standard form? It's similar to CALL
statement, but I am sorry, it's inconsistent, and it do problems
mainly for beginners.

Regards
Pavel

Re: PL/pgSQL and SETOF

From
"Merlin Moncure"
Date:
On Dec 3, 2007 3:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > > no. Your simplest version is historic relict and is available only in
> > > sql language. I am not sure, maybe in C language too.
> >
> > It is extremely useful to be able call functions in this way.  I
> > really wish it were possible to do this with pl/sql functions as
> > well...
> >
>
> where is this form better, than standard form? It's similar to CALL
> statement, but I am sorry, it's inconsistent, and it do problems
> mainly for beginners.

as you noted, a principal draw of this syntax isf invoking functions
like procedures.  if we had true stored procedures, this argument
would probably go away.  However, it also allows expressing some
complex queries in a simpler, faster way.  The payoff here is in some
really tricky cases.  Here is an example (note the expansion of the
set via the SRF 'explode array').  This isn't the only way to do this,
but is the best in PostgreSQL terms (AFAICT).

select q.item_lookup, is_child, p.* from item p
join
(
  select
    item_id as item_lookup,
    false as is_child,
    explode_array(parents) as item_id
    from item
  union all
    select
    l.item_id as item_lookup,
    true as is_child,
    r.item_id
    from item l
    join item r on r.parents between (l.parents || 0) and (l.parents
|| 2147483647)
) q using (item_id);

for the full context where I used that query, go to
http://merlinmoncure.blogspot.com/2007/09/one-of-my-favorite-problems-in.html

The really odd thing to me is that multiple SRF as field functions
expand to LCM number of rows.  OTOH, this gives us a brain busting
(seasonally themed) way to count up the 12 days of Christmas :D

create or replace function f(i int) returns setof int as
$$
    select generate_series(1, $1);
$$ language sql;

select sum((v).f) from (select f(f(12))) v;
 sum
-----
 364
(1 row)

merlin