Adding a value at specific index in Postgresql

Question

I need to assign the result of a function to an array like I used to do using Oracle:

DECLARE
  array array_t := array_t(); -- Initialise it
  v_idx NUMBER:=0;

BEGIN
    v_idx:= v_idx+1;
    array.extend;
    array(v_idx):= regexp_replace(
                  'HELLO WORLD', '(^|[^a-z0-9])' || 'HELLO' || '($|[^a-z0-9])', 
                  '\1' || 'GOOD BYE' || '\2', 1, 0,  'i');
    DBMS_OUTPUT.PUT_LINE('array(v_idx): '||array(v_idx)||' ,v_idx: '||v_idx);

END;
/

In PostgreSQL, I have tried this without successful results:

DECLARE
  x_array VARCHAR[];
  idx INTEGER:=1;

BEGIN 
  x_array[idx]:=string_to_array(SELECT REGEXP_REPLACE('HELLO WORLD','(^|[^a-z0-9])' || 'HELLO' || '($|[^a-z0-9])','\1' || 'GOOD BYE' || '\2','g')))
  RAISE NOTICE 'x_array:% ', x_array;
END;
/

How can you handle to store strings in an array and store/retrieve using index value? Should I use other object to be able to get/put elements by index?

Thanks a lot

Why are you calling string_to_array?

Bergi
– Bergi

2019-12-01 00:18:51 +00:00
Commented Dec 1, 2019 at 0:18 — Bergi
– Bergi, Commented Dec 1, 2019 at 0:18

Belayer · Accepted Answer · 2019-12-01 00:12:20Z

0

First, anonymous blocks in Postgres are identified by "DO" and are dollar quoted ($$...$$). Secondly your adding a string to an array not adding an array to an array (see concat array) so you don't require the String _to_array function, nor are you required to "Select" from the regex, just extract. With these in mind your anonymous block becomes:

do $$
declare
  x_array varchar[];
  idx integer:=1;
begin 
  x_array[idx]:=regexp_replace('HELLO WORLD','(^|[^a-z0-9])' || 'HELLO' || '($|[^a-z0-9])','\1' || 'GOOD BYE' || '\2','g');
  raise notice 'x_array:% ', x_array;
end; 
$$;

answered Dec 1, 2019 at 0:12

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Adding a value at specific index in Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related