plpgsql dynamic queries and optional arguments - Mailing list pgsql-general
From | Curtis Scheer |
---|---|
Subject | plpgsql dynamic queries and optional arguments |
Date | |
Msg-id | 031936836C46D611BB1B00508BE7345D049DB28A@gatekeeper.daycos.com Whole thread Raw |
List | pgsql-general |
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
pgsql-general by date: