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

JOINing SET returning function.

From: "Dawid Kuroczko" <qnex42(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: JOINing SET returning function.
Date: 2008-01-29 15:29:50
Message-ID: 758d5e7f0801290729t64bd9ad8oaeb05a521baa6ca5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hi,

I have a set returning function returning variable number of rows
(RETURNS SETOF RECORD).

I have a table which contains a list of input values for this SRF.

I want to write SQL which will return all the data found there.  A
simple test case:

a tble
CREATE TEMP TABLE list (n int);
INSERT INTO list VALUES (1),(3),(5),(6);

CREATE OR REPLACE FUNCTION test_set (IN OUT n int, OUT bar text)
RETURNS SETOF RECORD AS $$
  my $n = shift;
  for my $x (1..$n) {
    return_next { n=> $x, bar => '#' x $x }
  };
  return undef;
$$ LANGUAGE PLperl;

Now, this works fine:

SELECT select * from test_set(4);

            n | bar
---+------
 1 | #
 2 | ##
 3 | ###
 4 | ####

And this works fine too:
SELECT generate_series(1,n) FROM list;
 generate_series
-----------------
               1
               1
               2
               3
               1
               2
               3
               4
               5
               1
               2
               3
               4
               5
               6

However I would like to write a code which will work with test_set()
as it does with generate_series:

SELECT test_set(n) FROM list;
ERROR:  set-valued function called in context that cannot accept a set

...for which I've tried subselects and JOINs but somehow I fail to
make it work, for instance:
SELECT * FROM list, test_set(n);
ERROR:  function expression in FROM may not refer to other relations
of same query level

Is there an obvious solution (except wrapping it in some PL function)?

If it is not clear unitl that point, I want to do equivalent of:

SELECT * FROM test_set(1)
  UNION ALL SELECT * FROM test_set(3)
  UNION ALL SELECT * FROM test_set(5)
  UNION ALL SELECT * FROM test_set(6);

...except dynamically using "n" column from "list" table.

Of course this is a test case, not the original problem. :-)

 Regards,
    Dawid

pgsql-sql by date

Next:From: Premsun CholtanwanichDate: 2008-01-30 03:38:49
Subject: Re: Slow Query problem
Previous:From: Stuart BrooksDate: 2008-01-29 14:43:31
Subject: Slow GROUP BY query

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