How to create TYPE in PostgreSQL?

Question

How do I create a datatype in plpgsql? I'm new to PostgreSQL, i'm converting oracle procedures into postgresql functions. They have created a type in oracle something like:

TYPE t_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

Then declared & used it in the procedure like:

strings t_array;
strings(1) := lv_str;

I want to create t_array type in plpgsql and use it in functions, how can I create such type or is there any inbuilt type to achieve this functionality in plpgsql?

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You don't need to create a type for that in PL/pgSQL, just declare an array variable:

declare
  l_strings text[];
begin
  l_string[1] := 'This is the first string';
  l_string[2] := 'This is the second string';
end;

Arrays can be used as parameters as well:

create function do_something(p_some_number integer, p_strings text[])
  returns ..
as
...

Call it using do_something(42, array['one', 'two'])

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 27, 2019 at 9:20

user330315

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

Collectives™ on Stack Overflow

How to create TYPE in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related