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

Question

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?

Answer 1

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/[email protected]

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();

Answer 2

Using a loop to return the result of a query is slow and inefficient. The overhead of PL/pgSQL is not even required for this.

The best solution is:

create or replace function fn_list(vname varchar) 
  returns table(id integer, name text, status text) 
as $$
  SELECT id,name,status 
  from usuario 
  WHERE name= vname;
$$ language sql;

If PL/pgSQL is needed because some other procedural code needs to run before the query, then return query should be used instead of a loop:

create or replace function fn_list(vname varchar) 
  returns table(id integer, name text, status text) 
as $$
begin
  -- do some work....
  return query
    SELECT id,name,status 
    from usuario 
    WHERE name= vname;
end;
$$ language plpgsql;

---

Then call it using:

select *
from fn_list('Arthur');