From: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
To: pgsql-sql@postgresql.org
Subject: Help with function
Date: Mon, 20 Sep 2004 16:36:00 -0400
Message-ID: <NX13783b-6a515768@companiongroup.com> (raw)
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?
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
Subject: Re: Help with function
In-Reply-To: <NX13783b-6a515768@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