sql - 如何访问嵌套用户定义类型的字段？

Question

如何访问嵌套的用户定义类型的字段？当我尝试使用点符号时，它失败了:

ERROR:  "v_zoo.bear_object.animal_name" is not a known variable
LINE 8:  v_zoo.bear_object.animal_name='Mishka';

例如，我如何“编译”这段代码？

sqls $ cat animal.sql 
DROP TYPE IF EXISTS zoo_t CASCADE;
CREATE TYPE zoo_t AS (
    wolf_object         animal_t,
    bear_object         animal_t

);
DROP TYPE IF EXISTS animal_t CASCADE;
CREATE TYPE animal_t AS (
    animal_id           integer,
    animal_color        varchar,
    animal_name         varchar
);
CREATE OR REPLACE FUNCTION animal_func()
RETURNS void AS $$
DECLARE
    v_animal        animal_t;
    v_zoo           zoo_t;
BEGIN
    v_animal.animal_name:='Chupacabras';
    v_zoo.bear_object.animal_name='Mishka';
END;
$$ LANGUAGE PLPGSQL;

尝试运行它:

sqls $ psql dev < animal.sql 
DROP TYPE
CREATE TYPE
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to composite type zoo_t column wolf_object
drop cascades to composite type zoo_t column bear_object
DROP TYPE
CREATE TYPE
ERROR:  "v_zoo.bear_object.animal_name" is not a known variable
LINE 8:  v_zoo.bear_object.animal_name='Mishka';
         ^
 sqls $ 

Answer 1

看起来像是 PL/PgSQL 缺陷。在普通 SQL 中，您可以使用

test=> WITH x(zoo) AS (VALUES(ROW( ROW(1, 'red', 'panda')::animal_t, ROW(2, 'black', 'bear')::animal_t )::zoo_t))
SELECT (zoo).bear_object.animal_color FROM x;
 animal_color 
--------------
 black
(1 row)

但是 pl/pgsql 不接受相同的形式:

test=> CREATE OR REPLACE FUNCTION animal_func()
RETURNS void AS $$
DECLARE
    v_animal        animal_t;
    v_zoo           zoo_t;
BEGIN
    v_animal.animal_name:='Chupacabras';
    (v_zoo).bear_object.animal_name='Mishka';
END;
$$ LANGUAGE PLPGSQL;
ERROR:  syntax error at or near "("
LINE 8:     (v_zoo).bear_object.animal_name='Mishka';

所以我认为这是一个错误/疏忽/限制。考虑在 pgsql-bugs 上提出它。

您可以通过将其解压缩到一个临时变量、修改它并再次存储来访问它，但它的效率低得可怕。

CREATE OR REPLACE FUNCTION animal_func()
RETURNS void AS $$
DECLARE
    v_animal        animal_t;
    v_zoo           zoo_t;
BEGIN
    v_animal := v_zoo.bear_object;
    v_animal.animal_name := 'Mishka';
    v_zoo.bear_object := v_animal;
END;
$$ LANGUAGE PLPGSQL;

就是说:我不建议使用 plpgsql 来做这种伪 OO 的事情。 SQL 不是 OO 并且不能很好地处理嵌套的用户定义的复合类型。修改事物的效率非常低 - SQL 中的大多数事物都是不可变的，因此当您修改值时会生成新副本。迭代更改和过程代码的效果非常糟糕。

您应该设法处理集合和关系。一次构造新值，不要迭代修改它们并逐个设置字段。使用关系而不是让一个对象包含另一个对象。

此外，请放弃匈牙利符号。呃。