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().

Historicity

I think this is currently not possible. 9.3 will support lateral if I'm not mistaken – Euhemerus

I took the liberty to add the missing type numeric in the column definition list. Please check if that's what you actually meant. – Echevarria

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.

Echevarria

Ahh, brilliant! I see what you mean in SETOF record. I should change that in my function. – Landlubber

Thanks @Erwin. I'd like to try your "returns table" option, but it's only available since Postgres 8.4. I'm using 8.3. One more reason to upgrade my production servers... – Historicity

@franbenz: Upgrading would be a very good idea, but I've added a section for outdated versions, just in case. – Echevarria

I just tried your last option. I was thinking compute_prices would be called as many times as records where returned by the query over table items(select count(1) from items i where i.type=404). Since the full query was taking too long, I added a "raise notice" to compute_prices. The notice appeared a lot more than expected. About 20 calls were repeated with the same i.id value, and there were 20 repeated calls for some i.id values that where not going to be returned by the query over the items table. I don't understand why this is happening. – Historicity

I really can't comment on the ways of PostgreSQL 8.3. I haven't worked with it since 8.4 came out. I reiterate: upgrading to the current version is your best option. But, again, if you are stuck with 8.3 you can apply filters in a sub-SELECT and then call the function in the outer SELECT. Since compute_prices_wrapper() can return multiple rows, you would have to apply / repeat aggregate functions in the outer SELECT, though. – Echevarria

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.

Sherlock

LEFT JOIN ON TRUE is different from a CROSS JOIN when the right table return no rows, in which case the LEFT JOIN construct preserves the rows from the left table. But your simplified alternative happens to do the right thing anyway: "no row" results in NULL values. However, it seems like the OP's function might return an anonymous record (since he provides a column definition list). You cannot use (record).* notation in this case. – Echevarria

@Erwin: good point about the left join against the empty table. As for the function return's type the questions says "The compute_prices sp returns a setof record" so it's explicit about that. – Selfforgetful

If the function is defined with RETURNS SETOF record (set of anonymous records), your query will not work. You cannot expand anonymous records with (record).* syntax. It requires a column definition list, but I don't know of a way to fit that into this kind of query. I wrote an answer for that. – Echevarria

@Erwin: I know, this is why I'm telling to change the return type, which is the actual problem here from my point of view. My answer assumes that the asker choosed SETOF RECORD without knowing that it was going to be problematic. – Selfforgetful

Right, I overlooked that a bit in my last comment. Very useful answer. – Echevarria

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.

Landlubber

I fail to see the benefit compared to just SELECT * FROM logging.list_failed_jobs2(). Also, 1=1 is just an awkward replacement for TRUE, used in databases lacking a proper boolean type, like Oracle. Rather pointless in Postgres. – Echevarria

I work in an environment that include DBs like Oracle, so I stick with things that work everywhere. The OP wanted to see an example of a stored procedure call returning a SETOF type joined with a regular table, and this works. No, the statement serves no practical purpose itself; it's just to demonstrate syntax. But I see what you're saying. I'll edit. – Landlubber

Nope, the added example won't work. You cannot reference i in the function call on the same query level. You'd need the already mentioned LATERAL for that (introduced in the upcoming version 9.3). – Echevarria

Ahh, I wondered about that, but have never had a chance to try it. I don't have a suitable SP to play with right now. I guess I'm out of ideas. – Landlubber