4

For example:

create type t1 as (a int2, b text, c boolean);
create type t2 as (a t1, b boolean);
create type t3 as (a t2, b int4);

Now, how can I do the following for a variable x of type t3:

x.a.a.c := true;
y := x.a.a.c;

The first gives me an error.

  • Good question. For a one level composite, it's straightforward: x.a := z.a ; would work. It doesn't when you're some levels deeper... dbfiddle here. I thought this Accessing Composite Types would help, but it doesn't. – joanolo Jul 19 '17 at 6:53
3

There's one (awful, in my opinion) workaround... you can access only one level down at a time. Use intermediate variables.

DO LANGUAGE PLPGSQL
$$
DECLARE 
    x t3 ;
    x2 t2 ;
    x1 t1 ;
BEGIN
    -- x.a.a.c := true;   -- Syntax error

    x2 := x.a ;
    x1 := x2.a ;
    x1.c := true ;
    x2.a := x1 ;
    x.a := x2 ;
END ;
$$

dbfiddle here

2

You can assign the row type as a whole, which does what you ask for. But it also overwrites every other (sub-)field. (I use NULL for the rest.) See x in the demo.

You can also assign a whole composite type value to a column at the outer level. This preserves all other column values in the outer level. See y in the demo.

But you cannot do the same for nested composite types. See z in the demo.

DO
$$
DECLARE 
   x t3;
   y t3;
   z t3;
BEGIN
   x      := '("(""(,,true)"",)",)'::t3;  -- works
   y.a    :=    '("(,,true)",)'::t2;      -- works
--(z.a).a :=      '(,,true)'::t1;         -- does not work
   RAISE NOTICE E'\nx: %\ny: %\nz: %', x, y, z;
END
$$

dbfiddle here
(Using an EXCEPTION to make the result visible in the fiddle.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.