How the PostgreSQL function returns the table query result set
This article mainly introduces "how the PostgreSQL function returns the table query result set". In the daily operation, I believe that many people have doubts about how the PostgreSQL function returns the table query result set. The editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the question of "how the PostgreSQL function returns the table query result set". Next, please follow the editor to study!
First, create a test table
Create table jason01.jason123 (aa varchar)
Insert into jason01.jason123 values ('aa')
Insert into jason01.jason123 values ('ab')
Insert into jason01.jason123 values ('bb')
Postgres=# select * from jason01.jason123
Aa
-
Aa
Ab
Bb
(3 rows)
Second, create function
1. Method 1: use% rowtype
CREATE OR REPLACE FUNCTION jason01.fun_resultset () RETURNS SETOF jason01.jason123 AS
$
DECLARE
R jason01.jason123%rowtype
BEGIN
FOR r IN SELECT * FROM jason01.jason123 where aa like'a%'
LOOP
RETURN NEXT r
END LOOP
RETURN
END
$$LANGUAGE plpgsql
Postgres=# select jason01.fun_resultset ()
Fun_resultset
-
(aa)
(ab)
(2 rows)
2. Method 2: use the record type
CREATE OR REPLACE FUNCTION jason01.fun_resultset1 () RETURNS SETOF jason01.jason123 AS
$
DECLARE
R record
BEGIN
FOR r IN SELECT * FROM jason01.jason123 where aa like'b%'
LOOP
RETURN NEXT r
END LOOP
RETURN
END
$$LANGUAGE plpgsql
Postgres=# select jason01.fun_resultset1 ()
Fun_resultset1
-
(bb)
(1 row)
Method 3: use cursor + record
CREATE OR REPLACE FUNCTION jason01.fun_resultset2 () RETURNS SETOF jason01.jason123 AS
$
DECLARE
C CURSOR FOR SELECT * FROM jason01.jason123 where aa like'a%'
R RECORD
BEGIN
FOR r IN C
LOOP
RETURN NEXT r
END LOOP
RETURN
END
$$LANGUAGE plpgsql
Postgres=# select jason01.fun_resultset2 ()
Fun_resultset2
-
(aa)
(ab)
(2 rows)
At this point, the study on "how the PostgreSQL function returns the table query result set" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
