plpgsql dynamic queries and optional arguments

[Curtis Scheer <Curtis@xxxxxxxxxx>]

I have a table that I would like to be able to retrieve information out of based on a combination of multiple columns and I would like to be able to do this through a plpgsql stored procedure. Right now I have multiple stored procedures that I am calling based on the values parameter values I pass them and I am using static sql. The problem with this is it doesn’t scale as well as I would like it to because when I add another column of information to the table that needs to be used for retrieval it adds another level of combinations.

 

Also, when dealing with null values with static sql I use the same exact sql statement except for the where clause containing the “column1 is null” versus “column1 = passedvalue”. Anyways, I have made a simple example procedure and table; any help would be greatly appreciated basically I would like to use dynamic sql instead of static but I have unsuccessfully been able to retrieve the results of a dynamic sql statement in a pgplsql procedure. Here is the example table and stored procedure.

 

CREATE TABLE public.foo

(

  fooid int4 NOT NULL DEFAULT nextval('foo_fooid_seq'::regclass),

  foo_date timestamp NOT NULL,

  footypeid int4 NOT NULL,

  footext varchar,

  CONSTRAINT pk_fooid PRIMARY KEY (fooid)

)

WITHOUT OIDS;

ALTER TABLE public.foo OWNER TO fro;

 

 

CREATE OR REPLACE FUNCTION public.get_nextfoo(pfoo_date "timestamp", pfoovalue int4, pfootext bpchar)

  RETURNS SETOF public.foo AS

$BODY$DECLARE

            rec foo%ROWTYPE;

    BEGIN

    if pfootext is null then

            SELECT

               *

            INTO

               rec

            FROM

               foo     

            WHERE

           foo_date = pfoo_date

               and foovalue = pfoovalue

               and footext is null   

               For Update;

    else

            SELECT

               *

            INTO

               rec

            FROM

               foo     

            WHERE

           foo_date = pfoo_date

               and foovalue = pfoovalue

               and footext = pfootext   

               For Update;

            end if;

    RETURN NEXT rec;

   return;

 END;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION public.get_nextfoo(pfoo_date "timestamp", pfoovalue int4, pfootext bpchar) OWNER TO fro;

 

insert into foo(foo_date,foovalue,footext) values('2006-08-15',1,'bar');

insert into foo(foo_date,foovalue,footext) values('2006-08-14',1,'bar');

insert into foo(foo_date,foovalue,footext) values('2006-08-15',2,'bar');

insert into foo(foo_date,foovalue,footext) values('2006-08-14',2,'bar');

insert into foo(foo_date,foovalue) values('2006-08-15',1);

insert into foo(foo_date,foovalue) values('2006-08-14',1);

insert into foo(foo_date,foovalue) values('2006-08-15',2);

insert into foo(foo_date,foovalue) values('2006-08-14',2);

 

 

 

Thanks,
Curtis