simple way of adressing user defined type elements in postgresql

Viewed 30

im converting Oracle plsql code to Postgresql code but im stuck....

given is following code:

Oracle user defined Type:

TYPE typ_type_name is RECORD (
  typ_elem1 VARCHAR,
  typ_elem2 BOOLEAN := FALSE,
);

TYPE tab_type_name is TABLE of typ_type_name index by VARCHAR(32);

Oracle Procedure:

procedure proc_name(par_name in out nocopy typ_name)
is 
  v_name varchar(32);
BEGIN
  v_name := 'example_text';

  par_name(v_name).typ_elem1 := 'more_text';
END;

what I tried in postgres so far:

  • created type <typ_type_name>
  • created table with column types: <typ_type_name> so i can do some selects and index them by name (the varchar column)

Problem:

the way I tried it I cant use it the way like in the Oracle Procedure with:

par_name(v_name).typ_elem1 := 'more_text';

i guess the problem is the '(v_name)'

so is there an easy/simpler way of using oracles par_name(v_name).typ_elem1 := 'more_text'; in postgresql?

1 Answers

You cannot to do in PostgreSQL. PostgreSQL doesn't support text indexed arrays (collections).

Related