Using stored procedure returning SETOF record in LEFT OUTER JOIN

Question

I'm trying to call a stored procedure passing parameters in a left outer join like this:

select i.name,sp.*
from items i
left join compute_prices(i.id,current_date) as sp(price numeric(15,2), 
          discount numeric(5,2), taxes numeric(5,2)) on 1=1
where i.type = 404;

compute_prices() returns a setof record.
This is the message postgres shows:

ERROR: invalid reference to FROM-clause entry for table "i"

...left join compute_prices(i.id,current_date)...

HINT: There is an entry for table "i", but it cannot be referenced from this part of the query.

This kind of query works in Firebird. Is there a way I could make it work by just using a query? I don't want to create another stored procedure that cycles through items and makes separate calls to compute_prices().

Answer 1

Generally, you can expand well known row types (a.k.a. record type, complex type, composite type) with the simple syntax @Daniel supplied:

SELECT i.name, (compute_prices(i.id, current_date)).*
FROM   items i
WHERE  i.type = 404;

However, if your description is accurate ...

The compute_prices sp returns a setof record.

... we are dealing with anonymous records. Postgres does not know how to expand anonymous records and throws an EXCEPTION in despair:

ERROR:  a column definition list is required for functions returning "record"

PostgreSQL 9.3

There is a solution for that in Postgres 9.3. LATERAL, as mentioned by @a_horse in the comments:

SELECT i.name, sp.*
FROM   items i
LEFT   JOIN LATERAL compute_prices(i.id,current_date) AS sp (
                       price    numeric(15,2)
                      ,discount numeric(5,2)
                      ,taxes    numeric(5,2)
                      ) ON TRUE
WHERE i.type = 404;

Details in the manual.

PostgreSQL 9.2 and earlier

Things get hairy. Here's a workaround: write a wrapper function that converts your anonymous records into a well known type:

CREATE OR REPLACE FUNCTION compute_prices_wrapper(int, date)
  RETURNS TABLE (
            price    numeric(15,2)
           ,discount numeric(5,2)
           ,taxes    numeric(5,2)
          ) AS
$func$
    SELECT * FROM compute_prices($1, $2)
    AS t(price    numeric(15,2)
        ,discount numeric(5,2)
        ,taxes    numeric(5,2));
$func$ LANGUAGE sql;

Then you can use the simple solution by @Daniel and just drop in the wrapper function:

SELECT i.name, (compute_prices_wrapper(i.id, current_date)).*
FROM   items i
WHERE  i.type = 404;

PostgreSQL 8.3 and earlier

PostgreSQL 8.3 has just reached EOL and is unsupported as of now (Feb. 2013).
So you'd better upgrade if at all possible. But if you can't:

CREATE OR REPLACE FUNCTION compute_prices_wrapper(int, date
           ,OUT price    numeric(15,2)
           ,OUT discount numeric(5,2)
           ,OUT taxes    numeric(5,2))
  RETURNS SETOF record AS
$func$
    SELECT * FROM compute_prices($1, $2)
    AS t(price    numeric(15,2)
        ,discount numeric(5,2)
        ,taxes    numeric(5,2));
$func$ LANGUAGE sql;

Works in later versions, too.

The proper solution would be to fix your function compute_prices() to return a well know type to begin with. Functions returning SETOF record are generally a PITA. I only poke those with a five-meter-pole.

Answer 2

Assuming the compute_prices function always return a record with 3 prices, you could make its return type to TABLE (price numeric(15,2), discount numeric(5,2),taxes numeric(5,2)), and then I believe what you want could be expressed as:

SELECT i.name, (compute_prices(i.id,current_date)).*
  FROM items i
WHERE i.type=404;

Note that its seems to me that LEFT JOIN ON 1=1 does not differ from an unconstrained normal JOIN (or CROSS JOIN), and I interpreted the question as actually unrelated to the left join.

Answer 3

I believe Daniel's answer will work also but haven't tried it yet. I do know that I have an SP called list_failed_jobs2 in a schema called logging, and a dummy table called Dual (like in Oracle) and the following statement works for me:

select * from Dual left join 
              (select * from logging.list_failed_jobs2()) q on 1=1;

Note, the SP call will not work without the parens, the correlation (q), or the ON clause. My SP returns a SETOF also.

Thus, I suspect something like this will work for you:

select i.name,sp.*
from items i
left join (select * from compute_prices(i.id,current_date)) as sp on 1=1
where i.type = 404;

Hope that helps.