PostgreSQL Oracle 兼容性之 TABLE、PIPELINED函數

標簽

PostgreSQL , 返回表 , 返回複合類型 , 返回遊標

背景

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)