Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.