Re: Multiple SRF parameters from query

Hi,

I am not able to understand "Returning only one row", since 'aaa' having 2 rows and 'bbb' having 3 rows and what criteria single row should be returned. Please let me know the expected result and I will try to find out a solution for it.

for set returning function the following link maybe helpful
http://techdocs.postgresql.org/guides/SetReturningFunctions

Regards,
R.Muralidharan

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Federico Pedemonte
Sent: Tuesday, May 31, 2005 8:23 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Multiple SRF parameters from query

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org