Assign Element To Index of Array with Attribute in PostgreSSQL

Question

Can I assign a value from cursor to index of array with attribute?

I have an oracle code like this :

cursor cursor1 is select name, value from table;

FOR loop1 IN cursor1 LOOP
array1.EXTEND;
array1(loop1).cur_name := cursor1.name;
array1(loop1).cur_value := cursor1.value;
END LOOP;

i tried to convert to postgresql like this, but it's getting error

CREATE FUNCTION function_name () RETURNS something AS $$
DECLARE 
  cursor1 cursor for select name, value from table;
  array1   text[];
BEGIN
  -- Do something
  ...

  FOR loop1 IN cursor1 LOOP
    array1[loop].cur_name := cursor1.name; --error here
    array1[loop1].cur_value := cursor1.value; -- error here
  END LOOP;

  -- Do something
  ...
  RETURN;
END;

is there any method to create an array with attibute name?

A text array does not have elements with .cur_name and .cur_value fields. Declare it of the expected composite type. — Bergi
– Bergi, Commented Feb 5, 2023 at 23:38
how to declare an element name on array? or is there any keyword that i can search? — Nishihata Daigo 西畑大吾
– Nishihata Daigo 西畑大吾, Commented Feb 5, 2023 at 23:47

Belayer · Accepted Answer · 2023-02-07 00:39:25Z

The Oracle function is returning a collection (An Associative Array if I remember correctly, but its been awhile). Postgres does NOT have collections, the closest data type is an array. However since your collection contains multiple columns, you need to create a UDT (user defined type}, then your function returns an array of that type. (Note I assumed the data types in the table. Correct as deeded.)

create type name_val as (name text, value integer); 

create or replace function function_name () 
    returns name_val[]
   language plpgsql
as $$
declare 
  cursor1 cursor for
          select name, value
            from test
           limit 10;  
        
  rec     record;
  array1  name_val[];  
  l_name_val name_val; 
begin
  -- do something
 

  for rec in cursor1
  loop
    l_name_val.name = rec.name;
    l_name_val.value = rec.value; 
    array1 = array1 || l_name_val;
  end loop;

  -- do something
  
  return  array1;
end;
$$;

There are a couple other option which avoid the cursor and looping altogether. Assuming you actually need any Array returned you can reduce the above function to a single sql statement:

create or replace function function_name3()
   returns name_val[]
  language sql
as $$
    select array_agg((name, value)::name_val) 
      from test 
     limit 10;
$$;

Demo Here

UPDATE:
I noticed that subsequent to my answer you update the question from for loop1 in 1 .. 10 ... to for rec in cursor1 ... thus removing the resulting row limitation. You accomplish the same by just removing the Limit 10 clause.

Collectives™ on Stack Overflow

Assign Element To Index of Array with Attribute in PostgreSSQL

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