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?
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');
SELECT fn_list('Arthur') FROM some_table 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();
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 |
--------------------------------------------------
*/
