Hi,
I am not able to understand "Returning only one row", since 'aaa' having 2 rows and 'bbb' having 3 rows and what
criteriasingle 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@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Federico Pedemonte
Sent: Tuesday, May 31, 2005 8:23 PM
To: pgsql-sql@postgresql.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@postgresql.org