你的位置:首页 > 数据库

[数据库]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%');

  

--函数 涂聚文 Geovin DuCREATE FUNCTION check_password(uname TEXT, pass TEXT)RETURNS BOOLEAN AS $$DECLARE passed BOOLEAN;BEGIN    SELECT (pwd = $2) INTO passed    FROM  pwds    WHERE  username = $1;    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 INTO str from DepartmentList where DepartmentID=did;return str;end;$$language plpgsql;--测试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  RETURN NEXT v_rec;  end loop;  return;END;$$LANGUAGE PLPGSQL;--测试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;BEGIN   return query(select DepartmentID,DepartmentName from DepartmentList where DepartmentID = in_id);  return;END;$$LANGUAGE PLPGSQL;--测试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 SETOF 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;--测试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)  AS$$BEGIN  subject_scramble := substring($1, 1,CAST(random()*length($1) As integer));  subject_char := substring($1, 1,1);END;  $$ LANGUAGE 'plpgsql' VOLATILE; --测试 select fn_plpgsqltestout('geovindu'); CREATE OR REPLACE FUNCTION fn_sqltestmulti(param_subject varchar,   OUT test_id integer,   OUT test_stuff text)   RETURNS SETOF record  AS$$  SELECT DepartmentID,DepartmentName     FROM DepartmentList where DepartmentName LIKE $1;$$ LANGUAGE 'sql' VOLATILE; --测试SELECT * FROM fn_sqltestmulti('%d%');--OUT takes precendence which is why we prefix the table columnsCREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(  param_subject varchar,   OUT test_id integer,   OUT test_stuff varchar)   RETURNS SETOF record  AS$$BEGIN  RETURN QUERY SELECT t.DepartmentID , t.DepartmentName     FROM DepartmentList As t   WHERE t.DepartmentName LIKE param_subject;END;$$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM fn_plpgsqltestmulti('%d%',1, 'd');