postgresql/lightdb PL/pgSQL return setof my_type、resturn setof record和TABLE的区别及动态SQL执行

在pg中，广泛的使用了表函数代替视图，返回集合有两种定义，setof和table。他们的区别在于table明确定义了字段名和类型，如下：

CREATE FUNCTION events_by_type_1(text) RETURNS TABLE(id bigint, name text) AS $$
    SELECT id, name FROM events WHERE type = $1;
$$ LANGUAGE SQL STABLE;

注：对于返回select into或其它非直接SQL结果的过程，returns table本质上是inout参数的别名。

而setof则依赖SQL编译器解析，如下：

CREATE FUNCTION events_by_type_2(text) RETURNS SETOF record AS $$
    SELECT id, name FROM events WHERE type = $1;
$$ LANGUAGE SQL STABLE;

setof record是无法被get_func/call/expr_result_type函数识别出返回类型的。

使用的时候要明确as一把，如下：

SELECT * from events_by_type_2('social') as (id bigint, name text);

否则会提示"ERROR: a column definition list is required for functions returning "record""。

zjh@postgres=# select f2,f1 from getfoo_record();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select f2,f1 from getfoo_record();
                          ^

这种方式还能执行动态SQL以及实现任意模式的行转列，如下：

CREATE OR REPLACE FUNCTION run_a_query(query TEXT)
RETURNS SETOF RECORD
AS $$
DECLARE
retval RECORD;
BEGIN
FOR retval IN EXECUTE query LOOP
RETURN NEXT retval;   -- 这是由枚举SetFunctionReturnMode决定
END LOOP ;
END;
$$ LANGUAGE PLPGSQL;

select * from run_a_query('select usename::text,usesysid::int
from pg_user') as
("user" text, uid int);

zjh@postgres=# CREATE OR REPLACE FUNCTION unnest_v(VARIADIC arr anyarray)
zjh@postgres-# RETURNS SETOF anyelement AS $$
zjh@postgres$# BEGIN
zjh@postgres$# RETURN QUERY SELECT unnest(arr);
zjh@postgres$# END;
zjh@postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
zjh@postgres=# select unnest_v(1,2,3,4);
 unnest_v 
----------
        1
        2
        3
        4
(4 rows)

另外一种方式是不要返回record而是具体的类型，例如：

CREATE TYPE footype AS (score int, term text);

CREATE FUNCTION foo() RETURNS SETOF footype AS $$
   SELECT * FROM ( VALUES (1,'hello!'), (2,'Bye') ) t;
$$ language SQL immutable;

SELECT * FROM foo();  

在pg 10+新版本中，本质上没有区别。return setof my_type会更合适一些，它鼓励重用类型、而不是随意的拼接。

对于集合类型，总结起来，在pg中有下列写法：

 

https://stackoverflow.com/questions/22423958/sql-function-return-type-table-vs-setof-records

对于oracle迁移过来的用户而言，如果希望仍然使用pl/sql语法，也可以使用lightdb的plorasql过程性语言，可以基本兼容oracle pl/sql语法。

oracle pl/sql面向对象总结可以参考：oracle pl/sql与lightdb plorasql面向对象支持

从23.2开始，支持预定义类型的嵌套表，oracle函数调用语法。

从lightdb 23.3开始，完全支持自定义类型（create type as of object、及成员函数、静态函数）的嵌套表和关联数组。