Re: Need help returning record set from a dynamic sql query

[I'm not going to even try to work out that mess to quote it]

The following works for me. You can even do it without dynamic sql (see
fun_orderreport1).

begin;
-- dummy up some tables for self contained example
create table orders (ordersid int, initiated date, company int, event int);
create table company (companyid int, companyname text);
create table event (eventid int, company int, eventname text);

-- dummy up some data
insert into company values (1,'COMPANY');
insert into orders values (1, current_date, 1, 1);
insert into event values(1, 1, 'EVENT');

-- make the function
CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer,
pmcompanyid integer, pmeventid integer)
RETURNS SETOF record AS
$BODY$
DECLARE
vResult record;
vSql TEXT = '
SELECT
ORDR.ORDERSID AS OrderID,
ORDR.INITIATED AS Order_Date,
COMP.COMPANYNAME AS Company_Name,
EVNT.EVENTNAME AS Event_Name
FROM
ORDERS ORDR
INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID
WHERE
ORDR.EVENT = EVNT.EVENTID ';
BEGIN
IF $1 IS NOT NULL THEN
vSql = vSql ||' AND ORDR.ORDERSID = '|| $1;
END IF;

IF $2 IS NOT NULL THEN
vSql = vSql ||' AND COMP.COMPANYID = '|| $2;
END IF;

IF $3 IS NOT NULL THEN
vSql = vSql ||' AND EVNT.EVENTID = '|| $3;
END IF;

FOR vResult IN EXECUTE vSql
LOOP
RETURN NEXT vResult;
END LOOP;

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

-- run it
create temp table foo on commit drop as
SELECT 1,* from fun_orderreport(NULL,NULL,NULL) a (orderid int,
order_date date, company_name text, event_name text)
union
SELECT 2,* from fun_orderreport(1,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 3,* from fun_orderreport(NULL,1,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 4,* from fun_orderreport(NULL,NULL,1) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 5,* from fun_orderreport(2,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 6,* from fun_orderreport(1,1,1) a (orderid int, order_date date,
company_name text, event_name text);

-- do the same thing without execute
CREATE OR REPLACE FUNCTION fun_orderreport1(pmorderid integer,
pmcompanyid integer, pmeventid integer)
RETURNS SETOF record AS
$BODY$
DECLARE
vResult record;
begin
for vResult in
SELECT
ORDR.ORDERSID AS OrderID,
ORDR.INITIATED AS Order_Date,
COMP.COMPANYNAME AS Company_Name,
EVNT.EVENTNAME AS Event_Name
FROM
ORDERS ORDR
INNER JOIN COMPANY COMP ON COMP.COMPANYID = ORDR.COMPANY
INNER JOIN EVENT EVNT ON EVNT.COMPANY = COMP.COMPANYID and
evnt.eventid = ordr.event
WHERE
ordr.ordersid is not distinct from coalesce($1, ordr.ordersid)
and comp.companyid is not distinct from coalesce($2, comp.companyid)
and evnt.eventid is not distinct from coalesce($3, evnt.eventid)
loop
RETURN NEXT vResult;
END LOOP;

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

-- run it
create temp table bar on commit drop as
SELECT 1,* from fun_orderreport1(NULL,NULL,NULL) a (orderid int,
order_date date, company_name text, event_name text)
union
SELECT 2,* from fun_orderreport1(1,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 3,* from fun_orderreport1(NULL,1,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 4,* from fun_orderreport1(NULL,NULL,1) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 5,* from fun_orderreport1(2,NULL,NULL) a (orderid int, order_date
date, company_name text, event_name text)
union
SELECT 6,* from fun_orderreport1(1,1,1) a (orderid int, order_date date,
company_name text, event_name text);

-- if this returns any rows there's a difference
(select * from foo except select * from bar)
union
(select * from bar except select * from foo);