Using stored procedure returning SETOF record in LEFT OUTER JOIN

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 iWHERE  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 iLEFT   JOIN LATERAL compute_prices(i.id,current_date) AS sp (                       price    numeric(15,2)                      ,discount numeric(5,2)                      ,taxes    numeric(5,2)                      ) ON TRUEWHERE 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 iWHERE  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.

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 iWHERE 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.

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 ileft join (select * from compute_prices(i.id,current_date)) as sp on 1=1where i.type = 404;

Hope that helps.