Subject: Multiple SRF parameters from query
From: Federico Pedemonte <fepede@fastwebnet.it>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Date: Tue, 31 May 2005 16:52:52 +0200
Message-Id: <1117551172.895.46.camel@protciv14.cmirl.arpal.org>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Hi All,

i'm having troubles trying to write a sql query using a Set Returning
Function.

I have a function foo (ID varchar) that returns a variable number of
records.
I have also a table (called Anagrafica) that contains a list of IDs to
be passed to the function foo.

As an example, just say that Anagrafica is like this:

Anagrafica

ID  | n
----+---
aaa | 1
bbb | 5
ccc | 9
ddd | 10
eee | 11

and foo returns this values 

select * from foo ('aaa')

a | b | c
--+---+---
1 | 2 | 3
5 | 9 | 1


select * from foo ('bbb')

a | b | c
--+---+---
4 | 0 | 0 
2 | 0 | 0
0 | 0 | 0 


what i would like to do is write an SQL query that looks something like
this (written in simplyfied-pseudo-sql)

SELECT FROM anagrafica
  WHERE n < 5 
    SELECT * FROM Foo (anagrafica.ID)	
  
and that gives as result a table that is the union of foo ('aaa') and
foo ('bbb')

a | b | c
--+---+---
1 | 2 | 3
5 | 9 | 1
4 | 0 | 0 
2 | 0 | 0
0 | 0 | 0 

I'm having success if foo returns only a row, but the problem is that
foo returns more than one.
I think i sould use a join but i couldn't find the right syntax for it.

I don't want to create a spefic PL/pgSQL function becase i need to have
freedom in selecting IDs from Anagrafica (the one i provided is just an
example, the real table is much more complex).

I would really appreciate any suggestion.

Best regards, 
	Federico.