Re: [GENERAL] plpgsql dynamic queries and optional arguments
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpgsql dynamic queries and optional arguments
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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 doesnt 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
Re: [GENERAL] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
