CODEWITHSUNDEEP
functionpostgresql
MitoCode
MitoCode

Reputation: 327

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

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?

Upvotes: 9

Views: 35770

Answers (3)

Josh Hibschman
Josh Hibschman

Reputation: 3714

Many answers here omit important parts of using functions. Here's an updated way of using functions in postgres (including declaration, variables, args, return values, and running). Below is an over-baked example of updating the tweet on the bottom right "blurb" with "hello world".

id (serial) pub_id (text) tweet (text)
1 abc hello world
2 def blurb 
-- Optional drop if replace fails below.
drop function if exists sync_tweets(text, text);

create or replace function sync_tweets(
    src_pub_id text, -- function arguments
    dst_pub_id text
) returns setof tweets as -- i.e. rows. int, text work too
$$
declare
    src_id    int; -- temp function variables (not args)
    dest_id   int;
    src_tweet text;
begin
    -- query result into a temp variable
    src_id := (select id from tweets where pub_id = src_pub_id);

    -- query result into a temp variable (another way)
    select tweet into src_tweet from tweets where id = src_id;

    dest_id := (select id from tweets where pub_id = dst_pub_id);
    update tweets set tweet=src_tweet where id = dest_id;

    return query -- i.e. rows, return 0 with return int above works too
        select * from tweets where pub_id in (src_pub_id, dst_pub_id);
end
$$ language plpgsql; -- need the language to avoid ERROR 42P13

-- Run it!
select * from sync_tweets('abc', 'def');

/*
  Outputs
   __________________________________________________ 
  |  id (serial)  |  pub_id (text)  |  tweet (text)  |
  |---------------|-----------------|----------------|
  |  1            |  abc            |  hello world   |
  |  2            |  def            |  blurb         |
  --------------------------------------------------
*/

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 12

Horus
Horus

Reputation: 1175

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

Upvotes: 9

Related Questions