Thread: 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
andI would like to be able to do this through a plpgsql stored procedure. Right now I have multiple stored procedures
thatI am calling based on the values parameter values I pass them and I am using static sql. The problem with this is
itdoesn't scale as well as I would like it to because when I add another column of information to the table that needs
tobe 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
containingthe "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
unsuccessfullybeen able to retrieve the results of a dynamic sql statement in a pgplsql procedure. Here is the example
tableand 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,
pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses
containedin 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