plpgsql dynamic queries and optional arguments

Curtis,

Here is an example function that uses dynamic sql.
I use it under 7.4.5

Hope this helps.

Allan

-- Function to delete old data out of the point tables.
-- tablename is a column in the points table that holds the name
-- of the table in which this points data is stored.

create or replace function delete_old() returns integer as '
declare
pt record;
count integer;
sql_str varchar(512);

begin
count := 0;
for pt in select * from points loop
sql_str := ''deleting from '' || pt.tablename || '' data older than '' || pt.savefor::varchar || '' days'';
-- raise notice ''%'', sql_str;
sql_str := ''delete from '' || pt.tablename || '' where dt < (now() - interval '''''' || pt.savefor::varchar || '' days'''')::timestamp;'';
execute sql_str;
count := count + 1;
end loop;

return count;
end;
' LANGUAGE plpgsql;

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Curtis Scheer
Sent: Wednesday, 16 August 2006 3:22 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] plpgsql dynamic queries and optional arguments

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

The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.

Allan,

Thanks for the reply I guess what I am actually looking for is an example of
a dynamic SQL select statement similar to how a static sql select can select
into a variable.

Thanks,
Curtis

Curtis,

Here is an example function that uses dynamic sql.
I use it under 7.4.5

Hope this helps.

Allan

On Wed, Aug 16, 2006 at 02:36:44PM -0500, Curtis Scheer wrote:

Thanks for the reply I guess what I am actually looking for is an example of
a dynamic SQL select statement similar to how a static sql select can select
into a variable.

In 8.1 you can select a single row or columns of a single row with
INTO:

EXECUTE 'SELECT * FROM foo' INTO rec;

Earlier versions don't support INTO with EXECUTE but you can use a
loop to achieve the same effect:

FOR rec IN EXECUTE 'SELECT * FROM foo' LOOP
-- do stuff with rec
END LOOP;

Here are links to the relevant documentation:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
Michael Fuhr