标签
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)
参考
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://technology.amis.nl/2014/03/31/using-table-functions-2/
