Multiple SRF parameters from query

From: Federico Pedemonte <fepede(at)fastwebnet(dot)it>
To: pgsql-sql(at)postgresql(dot)org
Subject: Multiple SRF parameters from query
Date: 2005-05-31 14:52:52
Message-ID: 1117551172.895.46.camel@protciv14.cmirl.arpal.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Browse pgsql-sql by date

  From Date Subject
Next Message Mya Mason 2005-05-31 15:44:43 new antidote found
Previous Message scm-master 2005-05-31 12:42:00 Управление Цепями Поставок, мастер-класс