Skip site navigation (1) Skip section navigation (2)

Help with function

From: "CHRIS HOOVER" <CHRIS(dot)HOOVER(at)companiongroup(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help with function
Date: 2004-09-20 20:36:00
Message-ID: NX13783b-6a515768@companiongroup.com (view raw or flat)
Thread:
Lists: pgsql-sql
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?

Responses

pgsql-sql by date

Next:From: Greg StarkDate: 2004-09-20 20:45:48
Subject: Re: COUNT(*) to find records which have a certain number of dependencies ?
Previous:From: T E SchmitzDate: 2004-09-20 19:50:34
Subject: Re: COUNT(*) to find records which have a certain number of

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group