CODEWITHSUNDEEP
postgresqltypesplpgsqlrecord
EAPL Hyderabad
EAPL Hyderabad

Reputation: 23

How to assign and extract table values defined in RECORD type

Here is sample code

CREATE TYPE entity AS
(
    record_init boolean,
    b_modified boolean,
    custom_name character varying(8000),
    customer_rec customer,     --- customer is a table 
    lang_pref boolean,
)

do 
$$
declare 
entity_rec entity;
Begin

    entity_rec.record_init := TRUE;
    entity_rec.customer_rec.customer_id := 100;
    raise notice ' Customer Id is %', entity_rec.customer_rec.customer_id;
end;
$$;

getting an error while referring customer_id from entity_rec.customer_rec

Upvotes: 0

Views: 410

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

PLpgSQL doesn't support complex expressions on left side of assign statement. You should to divide this operation to more operations (this issue is fixed in prepared PostgreSQL 14):

DECLARE
  entity_rec entity;
  customer_rec customer;
BEGIN
  entity_rec.record_init := TRUE;
  customer_rec.customer_id := 100;
  entity_rec.customer_rec := customer_rec;
  raise notice ' Customer Id is %', entity_rec.customer_rec.customer_id;
END;

Second issue can be dereference of nested records. Postgres by default uses schema schema.table.name. When you use nested composite values, then the parenthesis should be used:

postgres=# do $$
DECLARE
  entity_rec entity;
  customer_rec customer;
BEGIN
  entity_rec.record_init := TRUE;
  customer_rec.customer_id := 100;
  entity_rec.customer_rec := customer_rec;
  raise notice ' Customer Id is %', entity_rec.customer_rec.customer_id;
END;
$$;
ERROR:  missing FROM-clause entry for table "customer_rec"
LINE 1: SELECT entity_rec.customer_rec.customer_id
               ^
QUERY:  SELECT entity_rec.customer_rec.customer_id
CONTEXT:  PL/pgSQL function inline_code_block line 9 at RAISE

Correct:

postgres=# do $$
DECLARE
  entity_rec entity;
  customer_rec customer;
BEGIN
  entity_rec.record_init := TRUE;
  customer_rec.customer_id := 100;
  entity_rec.customer_rec := customer_rec;
  raise notice ' Customer Id is %', (entity_rec).customer_rec.customer_id;
END;
$$;
NOTICE:   Customer Id is 100
DO

Upvotes: 2

Related Questions