Shulou(Shulou.com)06/01 Report--

Oracle's PL/SQL provides the Pipelined Table Functions feature to return multiple rows of data to the caller, which can effectively improve performance.

In PostgreSQL, you can do this by using SETOF or RETURN NEXT in a function.

Oracle

Create a data table and insert data

TEST-orcl@DESKTOP-V430TU3 > drop table tweepiperowitterdrop table t_piperow * ERROR at line 1:ORA-00942: table or view does not existTEST-orcl@DESKTOP-V430TU3 > create table t_piperow (id int,c1 timestamp, c2 varchar2 (20), c3 number); Table created.TEST-orcl@DESKTOP-V430TU3 > TEST-orcl@DESKTOP-V430TU3 > insert into t_piperow (id,c1,c2,c3) 2 select rownum,sysdate,'test' | | rownum,123455.55 from dba_objects where rownum commit;Commit complete.TEST-orcl@DESKTOP-V430TU3 >

Create Type

TEST-orcl@DESKTOP-V430TU3 > CREATE OR REPLACE TYPE rec_t_piperow AS OBJECT (id int,c1 timestamp, c2 varchar2 (20), c3 number), 2 / Type created.TEST-orcl@DESKTOP-V430TU3 > CREATE OR REPLACE TYPE type_t_piperow AS TABLE OF rec_t_piperow; 2 / Type created.

Function realization

TEST-orcl@DESKTOP-V430TU3 > CREATE OR REPLACE FUNCTION func_piperow_demo1 RETURN type_t_piperow PIPELINED IS 2 BEGIN 3 FOR rec in (select * from t_piperow) LOOP 4 PIPE ROW (rec_t_piperow (rec.id,rec.c1,rec.c2,rec.c3)), 5 END LOOP; 6 RETURN; 7 END; 8 / Function created.

Query data

TEST-orcl@DESKTOP-V430TU3 > column c3 format 9999999999999.9999TEST-orcl@DESKTOP-V430TU3 > select * from table (func_piperow_demo1 ()) where rownum < 5 ID C 1 C 2 C 3-1 31-OCT-19 10.50. 38.0 test1 123455.5500 00000 AM 2 31-OCT-19 10.50.38.0 test2 123455.5500 00000 AM 3 31-OCT-19 10.50.38.0 test3 123455.5500 00000 AM 4 31-OCT-19 10.50.38.0 Test4 123455.5500 00000 AM

PostgreSQL

Let's take a look at the implementation of PG, creating tables and inserting data.

[local]: 5432 pg12@testdb=# drop table if exists tactile piperowistDROP TABLETime: 5.255 ms [local]: 5432 pg12@testdb=# create table t_piperow (id int,c1 timestamp, c2 varchar (20), c3 float); CREATE TABLETime: 4.711 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_piperow (id,c1,c2,c3) pg12@testdb-# select x now (), 'test' | | xMJ 123455.55 from generate_series (1500) x INSERT 0 500Time: 10.183 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=#

Function realization

The first way is to use SQL:

[local]: 5432 pg12@testdb=# CREATE OR REPLACE FUNCTION func_piperow_demo1 () RETURNS SETOF PUBLIC.t_piperowpg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# SELECT * FROM tincture piperowistpg12diagnostic test limit # $$pg12@testdb-# LANGUAGE SQL;CREATE FUNCTIONTime: 1.341 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# select func_piperow_demo1 () limit 5 Func_piperow_demo1-(1, "2019-10-31 11 test2,123455.55 09 test2,123455.55 27.222996", test1,123455.55) (2, "2019-10-31 11 11 purge 27.9 22 2996") (3) "2019-10-31 1111 Time: 1.038 ms [local]: 5432 pg12@testdb=#" (4, "2019-10-31 11 test5,123455.55", test4,123455.55) (5, "2019-10-31 1111 test5,123455.55"): 1.038 ms [local]: 5432

Second, use PL/pgSQL,RETURN QUERY

[local]: 5432 pg12@testdb=# CREATE OR REPLACE FUNCTION func_piperow_demo2 () RETURNS SETOF PUBLIC.t_piperowpg12@testdb-# ASpg12@testdb-# $$pg12@testdb$# BEGINpg12@testdb$# RETURN QUERY SELECT * FROM tincture piperowistpg12diagnostic test database # END;pg12@testdb$# $$pg12@testdb-# LANGUAGE PLPGSQL;CREATE FUNCTIONTime: 3.850 ms [local]: 5432 pg12@testdb=# select func_piperow_demo2 () limit 5 Func_piperow_demo2-(1, "2019-10-31 11 test2,123455.55 09 test2,123455.55 27.222996", test1,123455.55) (2, "2019-10-31 11 11 purge 27.9 22 2996") (3) "2019-10-31 1111 Time: 5.645 ms [local]: 5432 pg12@testdb=#" (4, "2019-10-31 11 test5,123455.55", test4,123455.55) (5, "2019-10-31 1111 test5,123455.55"): 5.645 ms [local]: 5432

Third, use PL/pgSQL,RETURN NEXT

[local]: 5432 pg12@testdb=# select func_piperow_demo3 () limit 5 Func_piperow_demo3-(1, "2019-10-31 11 test2,123455.55 09 test2,123455.55 27.222996", test1,123455.55) (2, "2019-10-31 11 11 purge 27.9 22 2996") (3) "2019-10-31 1111 Time: 5.588 ms [local]: 5432 pg12@testdb=#" (4, "2019-10-31 11 test5,123455.55", test4,123455.55) (5, "2019-10-31 1111 test5,123455.55"): 5.588 ms [local]: 5432

references

TABLE and PIPELINED functions of PostgreSQL Oracle compatibility

Pipelined in Oracle as well in PostgreSQL

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.