Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL Oracle 兼容性 之 TABLE、PIPELINED函数(流式返回、返回多行、返回表、返回任意结构)

6 minute read

背景

Oracle 通过table, pipelined函数，用于格式化返回类型为table的函数的结果。

Table function concepts

There a couple of steps to take when you are working with table functions. Like when you are working with normal tables you have to describe the way the records of the collection are set up.

例子:

-- 定义复合类型  
CREATE TYPE script_line AS OBJECT  
(line NUMBER   ,text VARCHAR2(32767))  
  
-- 定义表类型  
CREATE or replace TYPE script_lines AS TABLE OF script_line;  
  
-- 定义返回表类型的函数  
CREATE OR REPLACE FUNCTION createinsertscriptfor(  
tablename_in IN VARCHAR2 ) RETURN script_lines  
....  
  
-- 通过TABLE格式化返回表类型的函数的结果集  
select * from TABLE(createinsertscriptfor('xx'));

pipelinedb的功能与table类似，不过它是流式返回，不需要等所有结果接收完后再格式化输出。

Functions can be pipelined. This means the results will become available when they are produced. As soon as a result is available instead of adding it to the nested table it will be piped out of the function.

例子:

CREATE OR REPLACE FUNCTION createinsertscriptfor(  
tablename_in IN VARCHAR2 ) RETURN script_lines PIPELINED  
.....

PostgreSQL TABLE、PIPELINED兼容性

1、PostgreSQL 建表时，自动建立对应的表类型、表数组类型。

例子

postgres=# create table abc(id int, info text);  
CREATE TABLE  
  
自动建立对应的复合类型以及复合数组类型。  
postgres=# select * from pg_type where typname ~ 'abc';  
 typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput  | typoutput  | typreceive  |   typsend   | typmodin | typmodout |    ty  
panalyze    | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl   
---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+-----------+------------+-------------+-------------+----------+-----------+------  
------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------  
 abc     |        34201 |       10 |     -1 | f        | c       | C           | f              | t            | ,        |    34634 |       0 |    34635 | record_in | record_out | record_recv | record_send | -        | -         | -      
            | d        | x          | f          |           0 |        -1 |        0 |            0 |               |            |   
 _abc    |        34201 |       10 |     -1 | f        | b       | A           | f              | t            | ,        |        0 |   34636 |        0 | array_in  | array_out  | array_recv  | array_send  | -        | -         | array  
_typanalyze | d        | x          | f          |           0 |        -1 |        0 |            0 |               |            |   
(2 rows)  
  
将一个字符串转换为复合类型  
  
postgres=# select '(1,hello)'::abc;  
    abc      
-----------  
 (1,hello)  
(1 row)  
  
postgres=# select ('(1,hello)'::abc).id;  
 id   
----  
  1  
(1 row)  
  
postgres=# select ('(1,hello)'::abc).info;  
 info    
-------  
 hello  
(1 row)  
  
删表后自动删除  
  
postgres=# drop table abc;  
DROP TABLE  
postgres=# select * from pg_type where typname ~ 'abc';  
 typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput | typoutput | typreceive | typsend | typmodin | typmodout | typanalyze |  
 typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl   
---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+----------+-----------+------------+---------+----------+-----------+------------+  
----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------  
(0 rows)

2、PostgreSQL 支持自定义复合类型，定义复合类型时，自动建立对应的复合数组类型。

例子

postgres=# create type typ1 as (id int, c1 int, c2 date);  
CREATE TYPE  
postgres=# select * from pg_type where typname ~ 'typ1';  
 typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput  | typoutput  | typreceive  |   typsend   | typmodin | typmodout |    ty  
panalyze    | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl   
---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+-----------+------------+-------------+-------------+----------+-----------+------  
------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------  
 typ1    |        34201 |       10 |     -1 | f        | c       | C           | f              | t            | ,        |    34646 |       0 |    34647 | record_in | record_out | record_recv | record_send | -        | -         | -      
            | d        | x          | f          |           0 |        -1 |        0 |            0 |               |            |   
 _typ1   |        34201 |       10 |     -1 | f        | b       | A           | f              | t            | ,        |        0 |   34648 |        0 | array_in  | array_out  | array_recv  | array_send  | -        | -         | array  
_typanalyze | d        | x          | f          |           0 |        -1 |        0 |            0 |               |            |   
(2 rows)  
  
将一个字符串转换为复合类型  
  
postgres=# select '(1,2,20170901)'::typ1;  
       typ1         
------------------  
 (1,2,2017-09-01)  
(1 row)  
  
postgres=# select ('(1,2,20170901)'::typ1).*;  
 id | c1 |     c2       
----+----+------------  
  1 |  2 | 2017-09-01  
(1 row)

3、PostgreSQL 函数支持返回不定义结构的类型record，或者定义结构的类型table, type, comp type等。

例子1，返回record，在查询时对其结构化。

create or replace function ftest1() returns record as $$  
declare  
begin  
  return (1,2,3,4);  
end;  
$$ language plpgsql strict;  
  
postgres=# select * from ftest1() as t(c1 int, c2 int, c3 int, c4 int);  
 c1 | c2 | c3 | c4   
----+----+----+----  
  1 |  2 |  3 |  4  
(1 row)

例子2，返回record，同时定义OUT参数，在查询时无需结构化。

postgres=# DROP FUNCTION ftest1();  
DROP FUNCTION  
postgres=# create or replace function ftest1(OUT C1 INT, OUT C2 TEXT) returns RECORD as $$  
declare  
begin  
  c1:=1;   
  c2:='abcde';   
  return;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
postgres=# select * from ftest1();  
 c1 |  c2     
----+-------  
  1 | abcde  
(1 row)

例子，返回table，在查询时无需结构化

postgres=# create table abcd(id int, info text);  
CREATE TABLE  
  
postgres=# create or replace function ftest2() returns abcd as $$  
declare  
begin  
  return (1, 'hello')::abcd;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
postgres=# select * from ftest2();  
 id | info    
----+-------  
  1 | hello  
(1 row)

4、PostgreSQL 函数支持返回数组。

例子

postgres=# create or replace function ftest3() returns abcd[] as $$  
declare   
  res abcd[];  
begin  
  res := array[(1, 'hello')::abcd];   
  res := array_cat(res, array[(2,'digoal')::abcd]);  
  return res;   
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
postgres=# select * from ftest3();  
           ftest3             
----------------------------  
 {"(1,hello)","(2,digoal)"}  
(1 row)

5、PostgreSQL 函数支持返回多条记录。

例子

postgres=# create or replace function ftest4() returns setof abcd[] as $$  
declare   
  res abcd[];  
begin  
  res := array[(1, 'hello')::abcd];   
  res := array_cat(res, array[(2,'digoal')::abcd]);  
  return next res;   
  return next array_cat(res,res);   
  return;   
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
postgres=# select * from ftest4();  
                       ftest4                          
-----------------------------------------------------  
 {"(1,hello)","(2,digoal)"}  
 {"(1,hello)","(2,digoal)","(1,hello)","(2,digoal)"}  
(2 rows)

6、PostgreSQL 函数支持返回游标（对应Oracle pipeline流式返回）。

postgres=# create or replace function ftest5() returns refcursor as $$  
declare  
  res refcursor := 'cur_1';  
begin   
  open res for select relname, relkind, relpages from pg_class ;  
  return res;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
  
postgres=# begin;  
BEGIN  
postgres=# select * from ftest5();  
 ftest5   
--------  
 cur_1  
(1 row)  
  
postgres=# fetch next from cur_1;  
 relname | relkind | relpages   
---------+---------+----------  
 seq     | S       |        1  
(1 row)  
  
postgres=# fetch next from cur_1;  
      relname      | relkind | relpages   
-------------------+---------+----------  
 idx_train_order_1 | i       |        1  
(1 row)

7、其他例子

create or replace FUNCTION SPLIT (
	P_LIST CLOB,
	P_SEP VARCHAR2 := '|'
)
RETURN STR_SPLIT
PIPELINED IS 
	L_IDX PLS_INTEGER ;
	V_LIST VARCHAR2(32676) := P_LIST ;
BEGIN
	 LOOP
		L_IDX := INSTR(V_LIST, P_SEP);
		IF L_IDX > 0 THEN
			PIPEROW(SUBSTR(V_LIST, 1, L_IDX - 1));
			V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));
		ELSE
			PIPEROW(V_LIST);
			EXIT;
		END IF;
	 END LOOP;
END;

修改为

create or replace FUNCTION SPLIT (
	P_LIST CLOB,
	P_SEP VARCHAR2 := '|'
)
RETURN setof STR_SPLIT  -- 修改
IS 
	L_IDX PLS_INTEGER ;
	V_LIST VARCHAR2(32676) := P_LIST ;
BEGIN
	 LOOP
		L_IDX := INSTR(V_LIST, P_SEP);
		IF L_IDX > 0 THEN
			return next SUBSTR(V_LIST, 1, L_IDX - 1);  -- 修改
			V_LIST := SUBSTR(V_LIST, L_IDX + LENGTH(P_SEP));
		ELSE
			return next V_LIST;  -- 修改
			EXIT;
		END IF;
	 END LOOP;
END;

PostgreSQL SRF - 返回多行，返回任意结构

plpgsql可以非常方便的返回多行，使用起来比ORACLE更加简单。

1、函数返回定义

returns setof record

returns setof 自定义类型

returns setof 表名

2、plpgsql内，返回多行的语法

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

RETURN;

3、例子1、

postgres=# create or replace function demo(vsql1 text, vsql2 text) returns setof record as $$ 
declare
begin
  return query execute vsql1;
  return query execute vsql2;
  -- return next 表达式;
  -- 可以无限制调用
  return;
end;
$$ language plpgsql strict;
CREATE FUNCTION
postgres=# select * from demo('select 1', 'select 2') as t(id int);
 id 
----
  1
  2
(2 rows)

postgres=# select * from demo('select oid::text::int from pg_class limit 10', 'select 2') as t(id int);  -- 返回record类型的函数，都需要as 来定义返回内容的结构。
  id   
-------
 25041
 37447
 25383
 38089
 25393
 25395
 25396
 24706
 25498
 24709
     2
(11 rows)

4、小结

srf返回多行，定义函数时返回returns setof xxx即可。如果返回的是record类型，那么在查询时，需要使用alias定义一下返回内容的结构。

put_line可以换成raise notice '%', var;

参考

http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm#CHDCIEJG

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:821242100346022602

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm

https://technology.amis.nl/2014/03/31/using-table-functions-2/

《金融风控、公安刑侦、社会关系、人脉分析等需求分析与数据库实现 - PostgreSQL图数据库场景应用》

《PostgreSQL Oracle 兼容性之 - PL/SQL pipelined》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...