sql:postgresql9.3 Using RETURNS TABLE vs. Out parameters

Http://www.postgresonline.com/journal/archives/201-Using-RETURNS-TABLE-vs.-OUT-parameters.html

--http://www.postgresonline.com/journal/archives/201-using-returns-table-vs.-out-parameters.html--ver:9.3 Geovin Du Tu--returning a single record using SQL functioncreate OR REPLACE FUNCTION fn_sqltestout (param_subject text, p Os integer) RETURNS TABLE (subject_scramble text, Subject_char text) as$$ SELECT substring ($, 1,cast (random () *le    Ngth ($) As Integer), substring ($) as Subject_char; $$ LANGUAGE ' SQL ' volatile;--example useselect (Fn_sqltestout (' This is a test subject ', 1)). subject_scramble; SELECT subject_scramble, Subject_char from Fn_sqltestout (' The is a test subject ', 5);--same function but written in PLPGSQ L--plpgsql example--return one recordcreate OR REPLACE FUNCTION fn_plpgsqltestout (param_subject varchar) RETURNS TABLE ( subject_scramble varchar, Subject_char varchar) as$ $BEGIN subject_scramble: = substring ($, 1,cast (random () *length ($    ) (as varchar));    Subject_char: = substring ($); RETURN NEXT;    END; $$ LANGUAGE ' Plpgsql ' Vol.atile;--Example Useselect (Fn_plpgsqltestout (' This is a test subject '). subject_scramble;  SELECT subject_scramble, Subject_char from Fn_plpgsqltestout (' This is a test subject '); --test data to use--create TABLE testtable (id integer PRIMARY KEY, Test text); INSERT into TestTable (id,test) VALUES (1, ' Potato '), (2, ' Potato '), (3, ' Cheese '), (4, ' Cheese Dog ');--sql function returning multiple recordscreate OR REPLACE funct        ION Fn_sqltestmulti (param_subject varchar) RETURNS TABLE (test_id integer, test_stuff text) as$$ SELECT ID, test   From TestTable WHERE test like $1;$$ LANGUAGE ' sql ' VOLATILE; --Example Useselect (Fn_sqltestmulti (' cheese% ')). Test_stuff; SELECT Test_stuff from Fn_sqltestmulti (' cheese% ');--plpgsql function Returning multiple records--note RETURN QUERY was I Ntroduced in 8.3--variant 1CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti (param_subject varchar) RETURNS TABLE (test_i       d integer, Test_stuff text) as$ $BEGIN RETURN QUERY SELECT ID, test From TestTable WHERE test like param_subject; end;$$ LANGUAGE ' plpgsql ' volatile;--Test select * from Fn_plpgsqltestmulti (' cheese% ');--Variant 2 Use the If you need to Do something additional--or conditionally return values or more dynamic stuff--return QUERY are generally more succinct a nd fastercreate OR REPLACE FUNCTION fn_plpgsqltestmulti (param_subject varchar) RETURNS TABLE (test_id integer, Test_stu FF text) as$ $DECLARE var_r record; BEGIN for Var_r in (SELECT ID, test from testtable WHERE test like param_subject) LOOP test _id: = var_r.id;            Test_stuff: = Var_r.test;     RETURN NEXT; END LOOP;  end;$$ LANGUAGE ' plpgsql ' volatile;--example use--This is the legal in PostgreSQL 8.4+--(prior versions Plpgsql could not Be called the This is the SELECT (Fn_plpgsqltestmulti (' cheese% ')). Test_stuff; SELECT * from Fn_plpgsqltestmulti (' cheese% ');

--Functional Geovin ducreate function Check_password (uname text, pass text) RETURNS Boolean as $ $DECLARE passed Boolean;        BEGIN SELECT (pwd = $) into passed from PWDs WHERE username = $; RETURN passed;    end;$$ LANGUAGE plpgsql SECURITY definer--Set a secure search_path:trusted schema (s), then ' pg_temp '. SET search_path = admin, pg_temp; ---http://www.postgresql.org/docs/current/static/sql-createfunction.html CREATE OR REPLACE function f_ Getdepartmentname (did integer) returns varchar as $ $declare str varchar;beginselect departmentname to STR from departmen  TList where Departmentid=did;return str;end;$ $language plpgsql;--Test Select F_getdepartmentname (1) as name;--((select  Departmentname from departmentlist where DepartmentID = in_id) union (select name from test_result2 where id = in_id) CREATE OR REPLACE FUNCTION func_departmentmore (in_id integer) RETURNS setof varchar as$ $DECLARE v_name varchar; BEGIN for V_name in (select DEpartmentname from departmentlist where DepartmentID = in_id) loop RETURN NEXT v_name;   End Loop; Return end;$ $LANGUAGE plpgsql;---select Func_departmentmore (1),---CREATE OR REPLACE FUNCTION Func_departmentname_muti (in_id Integer) RETURNS setof record as$ $DECLARE V_rec record; BEGIN for V_rec in (select Departmentid,departmentname from departmentlist where DepartmentID = in_id) Loop RET   URN NEXT V_rec;   End Loop; Return end;$ $LANGUAGE plpgsql;--Test SELECT * from Func_departmentname_muti (1) t (departmentid integer,departmentname varchar); CREATE OR REPLACE FUNCTION func_departmentname_query (in_id integer) RETURNS setof RECORD as$ $DECLARE v_rec Record;beg   In return query (select Departmentid,departmentname from departmentlist where departmentid = in_id); Return end;$ $LANGUAGE plpgsql;--Test SELECT * from Func_departmentname_query (1) t (departmentid integer,departmentname varchar); ---http://www.postgresonline.com/journal/archives/129-Use-of-OUT-and-INOUT-ParameTers.htmlcreate OR REPLACE FUNCTION func_departmentname_out (in_id integer,out o_id integer,out o_name varchar) RETURNS SE TOF record as$ $DECLARE V_rec record; BEGIN for V_rec in (select Departmentid,departmentname from departmentlist where DepartmentID = in_id) Loop o_ ID: = V_rec.    DepartmentID; O_name: = V_rec.    Departmentname;   RETURN NEXT;   End Loop; Return end;$ $LANGUAGE plpgsql;--Test Select Departmentid,departmentname from Departmentlistselect * from Func_departmentname_  Out (1), select * from Func_departmentname_out (2),---CREATE OR REPLACE FUNCTION func_table (in_id int) RETURNS table (F1 int, F2 varchar) as $$ begin SELECT F1=departmentid, f2=departmentname from departmentlist where DepartmentID =        in_id;    End    $$ LANGUAGE SQL; SELECT * from DUP (42); CREATE OR REPLACE FUNCTION fn_plpgsqltestout (param_subject text, out subject_scramble text, out Subject_char text) A S $ $BEGIN subject_scramble: = substring ($, 1,cast (random () *lengTh ($) as Integer); Subject_char: = substring ($);    END; $$ LANGUAGE ' Plpgsql ' VOLATILE; --Test Select Fn_plpgsqltestout (' Geovindu '); CREATE OR REPLACE FUNCTION fn_sqltestmulti (param_subject varchar, out test_id integer, out test_stuff text) RE TURNS setof record as$$ SELECT departmentid,departmentname from departmentlist where departmentname like $1;$  $ LANGUAGE ' sql ' VOLATILE;  --Test SELECT * from Fn_sqltestmulti ('%d% ');--out takes precendence which are why we prefix the table columnscreate OR REPLACE FUNCTION Fn_plpgsqltestmulti (param_subject varchar, out test_id integers, out test_stuff varchar) RETURNS Setof record as$ $BEGIN RETURN QUERY SELECT t.departmentid, t.departmentname from departmentlist as T WHE RE t.departmentname like Param_subject; end;$$ LANGUAGE ' Plpgsql ' VOLATILE; SELECT * from Fn_plpgsqltestmulti ('%d% ', 1, ' d ');

　　

sql:postgresql9.3 Using RETURNS TABLE vs. Out parameters