How to return a table, rows or record from a function in PostgreSQL 9?

function, postgresql

Solution

I know that pipelined returns in Oracle does this, so I used that to find 'RETURN NEXT' from plpgsql:

http://www.postgresql.org/message-id/007b01c6dc31$ae395920$0a00a8c0@trivadis.com

Also on grokbase:

http://grokbase.com/t/postgresql/pgsql-performance/069kcttrfr/pipelined-functions-in-postgres

(Edit to add official documentation): http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html

Killer, I will have to make use of this myself.

Editing one more time to add in some demo code (directly from postgresql.org documentation):

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();

Problem

I have a table called `person` which has `id,name,status` and I want to return rows as a result of a function with 1 parameter (name). Can anyone help me? Please make it easy, because im very noob in PostgreSQL. This is my code from a normal function ``` create or replace function fn_list(vname varchar) returns void as $$ begin SELECT id,name,status from usuario WHERE name= vname; end; $$ language plpgsql; ``` I know I'm returning a void function but how can I do if I want a list of rows?

Original source