drop table inttable; create table inttable( myint integer); insert into inttable values(1); insert into inttable values(2); insert into inttable values(3); insert into inttable values(4); insert into inttable values(5); insert into inttable values(6); drop function getAllStrs(); drop table strtable; create table strtable( mystr varchar(20)); insert into strtable values('one'); insert into strtable values('two'); insert into strtable values('three'); insert into strtable values('four'); insert into strtable values('five'); create language plpgsql ; create or replace function hello_world() returns void as ' begin for i in 1 .. 3 loop raise notice ''Hello World!''; end loop; end ' language plpgsql; select hello_world(); -- explain analyze verbose select * from inttable; -- explain select * from inttable; -- -- Test recursion, per bug report 7-Sep-01 -- CREATE or replace FUNCTION recursion_test(int,int) RETURNS text AS ' DECLARE rslt text; BEGIN IF $1 <= 0 THEN rslt = CAST($2 AS TEXT); ELSE rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2); END IF; RETURN rslt; END;' LANGUAGE plpgsql; SELECT recursion_test(4,3); create or replace function play(char(40), int) returns integer as ' declare myname alias for $1; myid alias for $2; rec record; begin raise notice '' Hey your name is % and your id is % '', myname, myid; select into rec * from strtable where mystr = myname; if not found then raise notice '' Hey your name is not in strtable! ''; insert into strtable values(myname); else raise notice '' Hey your name % is alreay in strtable! '', rec.mystr; end if; return myid; end ' language plpgsql; select play('thava', 1); -- Simple SQL function. drop function mydup_sql(in int, out f1 int, out f2 text); CREATE or replace FUNCTION mydup_sql(in int, out f1 int, out f2 text) -- returns setof record AS ' SELECT $1, CAST($1 AS text) || '' is text '' ; ' LANGUAGE SQL; SELECT * FROM mydup_sql(40) ; -- SELECT mydup_sql(40) ; -- You want return type as composite type! Another way to do this! -- drop type dup_result; CREATE TYPE dup_result AS (f1 int, f2 text); CREATE or replace FUNCTION mydup_usertype(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM mydup_usertype(50); create or replace function ret_query1(out int, out int) returns setof record as ' begin $1 := -1; $2 := -2; return next; return query select myint, myint from inttable where myint = 1; return next; end; ' language plpgsql; select * from ret_query1(); -- Return type "setof inttable" -- will return all rows -- Return type "inttable" -- will return only one row -- Return type "setof integer" -- will return all rows. column unnamed. -- my_ints(out f1 int) will help name the column. -- -- Note: Following return types are supported only by plpgsql: -- Return type "setof record" -- will force to declare column definitions -- to be declared as: my_ints(out f1 int); -- Return type "record" -- will also force declare column definition as: -- my_ints(out f1 int); -- Only one rec will be returned. -- This is default, if you ommit "returns record". -- drop function my_ints(); create or replace function my_ints() returns inttable as ' select * from inttable; ' language sql; select * from my_ints(); create or replace function scan_ints() returns setof integer as ' declare c cursor for select * from inttable; x integer; begin open c; loop fetch next from c into x; if found then return next x; end if; move relative 1 in c; if not found then exit; end if; end loop; close c; end; ' language plpgsql; select * from scan_ints(); -- get diagnostics integer_var = ROW_COUNT; -- Note: for return type tablename = rowtype. -- However for variable, you have to say r tablename%rowtype; -- CREATE OR REPLACE FUNCTION getAllStrs() RETURNS SETOF strtable AS ' DECLARE r strtable%rowtype; BEGIN FOR r IN SELECT * FROM strtable LOOP -- can do some processing here RETURN NEXT r; -- return current row of SELECT END LOOP; RETURN; END ' LANGUAGE plpgsql ; SELECT * FROM getAllStrs(); -- OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1); -- Using cursors interactively! drop table test; CREATE TABLE test (col text); INSERT INTO test VALUES ('123'); INSERT INTO test VALUES ('456'); CREATE or replace FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test; RETURN $1; END; ' LANGUAGE plpgsql; BEGIN; SELECT reffunc('funccursor'); FETCH ALL IN funccursor; COMMIT; -- References: -- * Documentation at http://postgresql.org -- * http://sqlzoo.net -- * http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm