User-defined Types

Variables of user-defined types in stored procedures cannot be pushed down. If user-defined types need to be pushed down, use variables to receive elements of user-defined types.

Below is an example:

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 statement pushdown
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=# -- Variables receiving elements of user-defined types
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);  -- Use a variable to receive an element of the user-defined type.
gaussdb$#         SELECT c1 INTO a FROM tb1 WHERE c2 = b; -- The execution is successful.
gaussdb$#     END LOOP;
gaussdb$# END;
gaussdb$# /
CREATE PROCEDURE
gaussdb=# CALL proc1();
 proc1 
-------
 
(1 row)
gaussdb=# 
gaussdb=# -- User-defined type pushdown
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); -- User-defined types do not support pushdown. An error is reported.
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