From: | Bryan Walker <fnprez(at)furiousninja(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Functions/stored procedures and returning rows with plpgsql |
Date: | 2003-03-18 05:18:45 |
Message-ID: | 3E76AC35.9060904@furiousninja.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Though I'm loath to admit it, I come from a MS-SQL Server programming
background (insofar as stored procedures and databases go), and am used
to writing stored procedures for that particular database.
Currently, I'm putting together a web page using PHP for my scripting,
and PostgreSQL as my database, and instead of just putting all of my SQL
inline, I'm trying to get the closest thing I can to stored procedures
going.
At this time, I have a number of functions I've built using the language
plpgsql performing a number of tasks, but I'm now stuck. How do I get a
function/stored procedure/whatever the correct terminology is to return
a set of rows? I can't seem to select multiple rows into a variable of
type <tablename>, and I really just can't figure out how to get a
function to return that setof <tablename>%ROWTYPE.
My most recent try looks like:
CREATE FUNCTION foo(integer) RETURNS SETOF Table1 AS '
DECLARE
Results1 SETOF Table1;
BEGIN
SELECT * INTO Results1 FROM Table1 WHERE Table1.ID = $1;
RETURN Results1;
END;
' LANGUAGE 'plpgsql';
Please forgive any obvious syntax errors. Those I can fix. At this
point, I'm just looking for a nudge in the right direction on how to get
multiple rows out by doing a SELECT * FROM foo(5) or whatever.
Thanks in advance.
--Bryan
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-03-18 05:28:24 | Re: Functions/stored procedures and returning rows with |
Previous Message | Keiko Kondo | 2003-03-18 03:52:52 | Re: My contract has been renewed |