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 涂聚文 --returning a single record using SQL functionCREATE OR REPLACE FUNCTION fn_sqltestout(param_subject text, pos integer)   RETURNS TABLE(subject_scramble text, subject_char text)  AS$$  SELECT substring($1, 1,CAST(random()*length($1) As integer)) ,    substring($1, 1,1) 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('This is a test subject',5);--Same function but written in plpgsql--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, 1,CAST(random()*length($1) As varchar));  subject_char := substring($1, 1,1);  RETURN NEXT;END;  $$ LANGUAGE 'plpgsql' VOLATILE;-- 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 FUNCTION 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 introduced in 8.3-- variant 1CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(param_subject varchar)   RETURNS TABLE(test_id integer, test_stuff text)  AS$$BEGIN  RETURN QUERY SELECT id, test    FROM testtable WHERE test LIKE param_subject;END;$$ LANGUAGE 'plpgsql' VOLATILE;--测试select * from fn_plpgsqltestmulti('Cheese%');-- variant 2 use this if you need to do something additional-- or conditionally return values or more dynamic stuff-- RETURN QUERY is generally more succinct and fasterCREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(param_subject varchar)   RETURNS TABLE(test_id integer, test_stuff 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 legal in PostgreSQL 8.4+ -- (prior versions plpgsql could not be called this way)SELECT (fn_plpgsqltestmulti('Cheese%')).test_stuff;SELECT * FROM fn_plpgsqltestmulti('Cheese%');

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：sql:PostgreSQL9.3 Using RETURNS TABLE vs. OUT parameters

关键词：sql