Difficulties to use a Set Returning Function on a join

From: Alvaro Nunes Melo <al_nunes(at)atua(dot)com(dot)br>
To: PostgreSQL - SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Difficulties to use a Set Returning Function on a join
Date: 2004-09-13 22:03:03
Message-ID: 1095112983.6693.5.camel@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)atua(dot)com(dot)br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+

Browse pgsql-sql by date

  From Date Subject
Next Message Robert Davis 2004-09-15 01:21:27 explain analyze results are different for each iteration
Previous Message andrew 2004-09-13 18:13:08 Re: CREATE RULE ignored, what did I do wrong