sql:PostgreSQL9.3 Using RETURNS TABLE vs. OUT parameters-优快云博客

本文深入探讨了PostgreSQL中函数的创建与使用，包括SQL与PL/pgSQL语言下的函数定义，返回单条记录与多条记录的函数实现，以及使用OUT参数进行数据返回的方法。通过具体实例，展示了如何在PostgreSQL中灵活运用不同类型的函数来处理数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

http://www.postgresql.org/docs/9.4/interactive/index.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 function
CREATE 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 use
SELECT  (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 record
CREATE 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 use
SELECT  (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 records
CREATE 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 use
SELECT (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 1
CREATE 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 faster
CREATE 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 Du
CREATE 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;
begin
select 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.html
CREATE 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 DepartmentList

select * 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 columns
CREATE 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');