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、及成员函数、静态函数)的嵌套表和关联数组。