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.postgresql.org/docs/9.4/interactive/index.html

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
--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%');

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
--函数  涂聚文 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');

　　

posted @   ®Geovin Du Dream Park™  阅读(968)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示