Re: Newbie help needed on Functions

From: Masse Jacques <jacques(dot)masse(at)bordeaux(dot)cemagref(dot)fr>
To: 'Jim Steil' <jim(dot)steil(at)customcall(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Newbie help needed on Functions
Date: 2002-09-25 08:13:41
Message-ID: 0B87317EA62BD211A02A00A0C9DFB7EC9B5ED9@hermes.bordeaux.cemagref.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm not an expert, but I think that a function cannot directly return a rows
set. You have to use a cursor

http://www.postgresql.org/idocs/index.php?plpgsql-cursors.html
<http://www.postgresql.org/idocs/index.php?plpgsql-cursors.html>

I use the following (postgresql 7.2) to get multiple rows in a table, but
the table must exist before you run the function.

Don't forget double quotes (1+1) for the variable in the query

You have to install pl/pgsql language before ...

CREATE FUNCTION test(varchar) RETURNS varchar AS '

declare str alias for $1;

declare str_ok varchar(2);

begin

str_ok = 'OK'

drop table tmp_result;

create table tmp_result as select a.* from your_table a where
a.state=''str'';

return str_ok;

end;

' LANGUAGE 'pl/pgsql';

___________________________________________
Jacques Massé
Tel. 33 (0)5 57 89 08 11 - Fax 33 (0)5 57 89 08 01

-----Message d'origine-----
De : Jim Steil [mailto:jim(dot)steil(at)customcall(dot)com]
Envoyé : mardi 24 septembre 2002 19:59
À : pgsql-novice(at)postgresql(dot)org
Objet : [NOVICE] Newbie help needed on Functions

Hi:

I want to create a function that will return rows from a table based upon a
parameter that I pass.

Browse pgsql-novice by date

  From Date Subject
Next Message Maryam Bashir 2002-09-25 10:00:47 problem in gmake and make commands
Previous Message Nicolas Cartron 2002-09-25 07:03:18 Re: Question about cygwin/Postgre