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;


I was trying to call this function from psql using:
select test_func('asdf');

Which returned:

ERROR: Set-valued function called in context that cannot accept a set

WARNING: Error occurred while executing PL/pgSQL function test_func
WARNING: while casting return value to function's return type

What am I doing wrong? I'm in fairly new territory with this type of
functions. I normally have just written simple trigger function that log
table changes.

Thanks for any and all help.

Chris

P.S.

Is there a good website and/or book for learning the intricacies of pgsql?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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.html
http://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).

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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 [at] postgresql.org.comp
From: Stephan.Szabo[sszabo] [at] 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.html
http://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).

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Tue, 21 Sep 2004, CHRIS HOOVER wrote:

> 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?

Pretty much yes. You can use the
FOR <record> IN EXECUTE <sqlstring> LOOP
structure to run the query. The only thing is that
you have to put the values into the string not the name
of the parameters (probably using quote_literal).

So rather than
SQLStr := SQLStr || "where column = Param1";
you'd want something like:
SQLStr := SQLStr || "where column = " || quote_literal(Param1);

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings