From: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with function
Date: Tue, 21 Sep 2004 10:23:00 -0400
Message-ID: <NZb07058-646270b1@companiongroup.com> (raw)
Thanks a bunch for the pointers and help.
One other hopefully quick question.
How do you query using a variable containing the query?
I'm trying to build a select statment based upon what parameters are being
passed to the function.
somthing like this:
Declare
Param1 varchar;
Param2 varchar;
SQLStr varchar;
Table_rec Table%ROWTYPE;
Begin
SQLStr:="select * from table"
Param1:= $1;
Param2 :=$2;
if (Param1 is not null) then
SQLStr := SQLStr || "where column=Param1";
else
SQLStr := SQLStr || "where column=Param2";
end if;
SQLStr := SQLStr || ";"
for Table_Rec in SQLStr loop
return next Table_rec;
end loop;
return;
end;
Is this possible?
Thanks again for any help,
Chris
------------------( Forwarded letter 1 follows )---------------------
Date: Mon, 20 Sep 2004 13:51:09 -0700 (PDT)
To: chris.hoover
Cc: pgsql-sql@postgresql.org.comp
From: Stephan.Szabo[sszabo]@megazone.bigpanda.com.comp
Subject: Re: [SQL] Help with function
On Mon, 20 Sep 2004, CHRIS HOOVER wrote:
> I need some help writing a simple function.
>
> Due to some program limitations for a program I run the db's for, I'm having
> to write some simple functions to run some selects. However, I am not sure
> how to have them correctly return the record(s) selected and/or how to
> properly call them from sql.
>
> Would someone be so kind as to help me with this.
>
> Here is an example function:
>
> CREATE OR REPLACE FUNCTION "public"."test_func" (varchar) RETURNS SETOF
> "public"."test_tbl" AS'
> Declare
> PCN varchar;
> test_tbl_rec clmhdr%ROWTYPE;
>
> Begin
>
> PCN := $1;
>
> select into test_tbl_rec * from test_tbl where test_tbl.pcn = PCN;
> return test_tbl_rec;
>
> end;
> 'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
If you want to return sets of rows, you're going to need to loop over the
returned rows from the select using return next.
These links may help:
http://www.varlena.com/varlena/GeneralBits/26.htmlhttp://techdocs.postgresql.org/guides/SetReturningFunctions> I was trying to call this function from psql using:
> select test_func('asdf');
As a side note, plpgsql set returning functions cannot be called in a
select list, only in the from clause (the above links will have examples).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: CHRIS.HOOVER@companiongroup.com, sszabo@megazone.bigpanda.com
Subject: Re: Help with function
In-Reply-To: <NZb07058-646270b1@companiongroup.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox