自定义类型

存储过程中的自定义类型变量不支持下推，需要在下推场景使用时，用变量承接自定义类型的元素。

示例如下：

gaussdb=# CREATE SCHEMA best_practices_for_procedure;
CREATE SCHEMA
gaussdb=# SET CURRENT_SCHEMA=best_practices_for_procedure;
SET
gaussdb=# 
gaussdb=# CREATE TABLE tb1(c1 INT, c2 VARCHAR(20));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# INSERT INTO tb1 VALUES(1, 'a'),(2,'b'), (3, 'c');
INSERT 0 3
gaussdb=# -- SELECT语句下推执行
gaussdb=# EXPLAIN SELECT c1 FROM tb1 WHERE c2 = 'a';
                    QUERY PLAN                    
--------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Node/s: All datanodes
(2 rows)
gaussdb=# -- 变量承接自定义类型元素
gaussdb=# CREATE OR REPLACE PROCEDURE proc1() AS
gaussdb$# TYPE ta IS VARRAY(10) OF VARCHAR(30);
gaussdb$# v ta := ta();
gaussdb$# b VARCHAR;
gaussdb$# a INT;
gaussdb$# BEGIN
gaussdb$#     v(1) := 'a';
gaussdb$#     v(2) := 'b';
gaussdb$#     FOR i IN 1..v.count LOOP
gaussdb$#         b := v(i);  -- 变量承接自定义类型的元素
gaussdb$#         SELECT c1 INTO a FROM tb1 WHERE c2 = b; -- 执行成功
gaussdb$#     END LOOP;
gaussdb$# END;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL proc1();
 proc1 
-------
 
(1 row)
gaussdb=# 
gaussdb=# -- 自定义类型下推场景
gaussdb=# CREATE OR REPLACE PROCEDURE proc2() AS
gaussdb$# TYPE ta IS VARRAY(10) OF VARCHAR(30);
gaussdb$# v ta := ta();
gaussdb$# b VARCHAR;
gaussdb$# a INT;
gaussdb$# BEGIN
gaussdb$#     v(1) := 'a';
gaussdb$#     v(2) := 'b';
gaussdb$#     FOR i IN 1..v.count LOOP
gaussdb$#         SELECT c1 INTO a FROM tb1 WHERE c2 = v(1); -- 自定义类型不支持下推，执行报错
gaussdb$#     END LOOP;
gaussdb$# END;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL proc2();
ERROR:  Function v(integer) does not exist.
LINE 1: SELECT c1        FROM tb1 WHERE c2 = v(1)
                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT c1        FROM tb1 WHERE c2 = v(1)
CONTEXT:  PL/pgSQL function proc2() line 10 at SQL statement
gaussdb=# 
gaussdb=# DROP SCHEMA best_practices_for_procedure cascade;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table tb1
drop cascades to function proc1()
drop cascades to function proc2()
DROP SCHEMA