Help with function

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

Re: 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 5: Have you checked our extensive FAQ?

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

Re: Help with function

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

Re: Help with function

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 IN EXECUTE 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