Functions/stored procedures and returning rows with plpgsql

Поиск
Список
Период
Сортировка
От Bryan Walker
Тема Functions/stored procedures and returning rows with plpgsql
Дата
Msg-id 3E76AC35.9060904@furiousninja.com
обсуждение исходный текст
Ответы Re: Functions/stored procedures and returning rows with  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Keiko Kondo
Дата:
Сообщение: Re: My contract has been renewed
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Functions/stored procedures and returning rows with