CODEWITHSUNDEEP
sqlpostgresql
Nulik
Nulik

Reputation: 7360

How do I access a field of nested user defined types?

How do I access the fields of user defined types when they are nested ? When I tried it using dot notation it failed:

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

For example, how do I "compile" this code ?

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;

Trying to run it:

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 $

Upvotes: 0

Views: 1297

Answers (2)

abby37
abby37

Reputation: 647

To access the composite type in postgres,

SELECT (v_zoo).wolf_object.animal_color FROM TABLE;

Accessing composite value type was not there in postgres when this question was asked but we can simply do it by sql way now. It got supported by postgres from 9.6.

To know more about it, you can read here.

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324455

Looks like a PL/PgSQL deficiency. In normal SQL you can do so with 

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)

but pl/pgsql doesn't accept the same form:

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';

so I think it's a bug/oversight/limitation. Consider raising it on pgsql-bugs.

You can access it by unpacking it into a temp var, modifying it, and storing it again, but it's hideously inefficient.

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;

That said: I don't suggest using plpgsql to do this kind of pseudo-OO stuff. SQL is not OO and doesn't play well with nested user defined composite types. It's very inefficient to modify things - most things in SQL are immutable so new copies are made when you modify values. Iterative change and procedural code works appallingly poorly.

You should seek to work with sets and relations. Construct new values in one pass, don't iteratively modify them and set fields one by one. Use relationships rather than having one object contain another.

Also, please drop the hungarian notation. Ugh.

Upvotes: 4

Related Questions