TYPE IS TABLE OF, from Oracle to PostgreSQL

Question

I'm currently migrating some procedures from Oracle to Postgres, those procedures are using a type that was created to handle an unknown number of values:

TYPE array_text IS TABLE OF VARCHAR2 (50);

So they can declare a variable like:

myValues in array_text;

And use it later this way:

myValues(1) := 'VALUE1';
myValues(2) := 'VALUE2';
...

So I have been learning and trying to replicate this behavior using Postgres, but until now I can't find a properly way. I tried creating a composite type like:

CREATE TYPE array_text AS (
    val varchar(50)
);

Or

CREATE TYPE array_text AS (
    v varchar(50)[]
);

However I can't use them the same way as the original is used, and actually I couldn't create a successful test.

How could I get this behavior or something similar? So I can set and get values and even count its length like in Oracle

score 5 · Accepted Answer · 2018-09-13 06:08:03Z

5

It's much simpler in Postgres than in Oracle - you don't need to define a collection type. Just declare an array.

The sensible and most efficient replacement of TYPE array_text IS TABLE OF VARCHAR2 (50); is text[] in Postgres.

To get the length of the array use cardinality(). You don't need a special method to extend its capacity, just assign a value to the index you want. The only major difference is, that there is no (easy) way to shrink an array in Postgres.

declare
   myValues text[];
   num_values int;
begin

  myValues[1] := 'VALUE1';
  myValues[2] := 'VALUE2';

  num_values := cardinality(myvalues); // yields 2

  myValues[3] := 'VALUE3';
  num_values := cardinality(myvalues); // yields 3
end;

To extend the answer: you can also use table types like that. So you don't need to define an intermediate "table of" collection type, just to use a table's type for an array.

create table person
(
  id integer,
  firstname text,
  lastname text
);

Then you can use

do
$$
declare
  people_list person[];
begin
  people_list[1] := row(42, 'Arthur', 'Dent')::person;
  raise notice 'Name %', people_list[1].firstname;
end;
$$

edited Sep 13, 2018 at 6:08

answered Sep 13, 2018 at 6:02

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

MT0 Over a year ago

Oracle also has the CARDINALITY() function - its just that you can also use myValues.COUNT and it appears to be the better known Oracle solution.

Jasen · Accepted Answer · 2018-09-13 05:56:03Z

3

postgres doesn't allow tables (or composite types) with unnamed columns

unlike tables arrays can be multidimensional, and also can start other than at [1] or [0], so you may need to check that the input is of the correct form (unless incorrect input implicitly causes an error)

CREATE TYPE array_text AS (
    val varchar(50)[]
);

but this creates a composite type with one attribute which is an array... so you need to used .val to access the array.

jasen=# do $$ 
declare a array_text;
begin 
   a.val[0]='won';
   a.val[1]='too';
   raise notice 'len=%',array_length(a.val,1); end;
$$;
NOTICE:  len=2
DO

but if you declare the array in-line you can just use a simple name.

jasen=# do $$ 
declare a varchar(50)[];
begin 
   a[5]='eigh';
   a[6]='bee';
   raise notice 'len=%',array_length(a,1); end;
$$;
NOTICE:  len=2
DO

edited Sep 13, 2018 at 5:56

answered Sep 13, 2018 at 5:07

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

1 Comment

KESO Over a year ago

With your example I could finally run my test, this time I'm going to choose the simply array way to maybe avoid complexity when calling them from Java.

Collectives™ on Stack Overflow

TYPE IS TABLE OF, from Oracle to PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related