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

Source: Internet
Author: User
Tags postgresql volatile

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.