From: Alvaro Nunes Melo <al_nunes@atua.com.br>
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Subject: Difficulties to use a Set Returning Function on a join
Date: Mon, 13 Sep 2004 19:03:03 -0300
Message-ID: <1095112983.6693.5.camel@localhost> (raw)
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 |
+---------------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: al_nunes@atua.com.br
Subject: Re: Difficulties to use a Set Returning Function on a join
In-Reply-To: <1095112983.6693.5.camel@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox