Re: Need help returning record set from a dynamic sql query - Mailing list pgsql-general

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