Subject: Difficulties to use a Set Returning Function on a join
From: Alvaro Nunes Melo <al_nunes@atua.com.br>
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Content-Type: text/plain
Message-Id: <1095112983.6693.5.camel@localhost>
Mime-Version: 1.0
Date: Mon, 13 Sep 2004 19:03:03 -0300
Content-Transfer-Encoding: 7bit

Hi, 

I have a Set Returning Function that works great when called alone, but
I'm having problems to join it with another table and use this table's
primary key as the parameter for the SRF.

I'm sending it with the names in Brazilian Portuguese, but I think it
might be compreensible english speakers.

This works fine:
SELECT * FROM obtem_situacao_pedido(1);
 cd_pedido | cd_pedido_situacao | id_tipo 
-----------+--------------------+---------
         1 |                106 |       1
(1 row)

But these doesn't:
SELECT p.*, hsp.*
FROM pedido p
     LEFT OUTER JOIN obtem_situacao_pedido(p.cd_pedido) hsp ON
hsp.cd_pedido = p.cd_pedido; 
ERROR:  Relation "p" does not exist

SELECT p.*, hsp.*
FROM obtem_situacao_pedido(p.cd_pedido) hsp
     RIGHT OUTER JOIN pedido p ON hsp.cd_pedido = p.cd_pedido;
ERROR:  Relation "p" does not exist

SELECT osp.*
FROM pedido p
      JOIN obtem_situacao_pedido(pedido.cd_pedido) osp USING(cd_pedido);
NOTICE:  Adding missing FROM-clause entry for table "pedido"
ERROR:  FROM function expression may not refer to other relations of
same query level

Thanks in advance,

-- 
+---------------------------------------------------+
|  Alvaro Nunes Melo    Atua Sistemas de Informacao |
| al_nunes@atua.com.br        www.atua.com.br       |
|    UIN - 42722678            (54) 327-1044        |
+---------------------------------------------------+