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

[Klint Gore <kgore4@xxxxxxxxxx>]

[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);