Selecting from multidimensional array parameter in Postgres

Question

I have a function that does an insert in a parent table then inserts into a child table. The last parameter is an array of variable length. I use this to do the insert into the child table by sending an array of comma delimited pairs and parsing these out.

create or replace function neil_test(time_stamp timestamp with time zone, type_id text, raw_message text,field_values text[])
returns void

AS $$
    DECLARE 
    last_message_id bigint;
    x text;
BEGIN
    INSERT INTO message(time_stamp,type_id,raw_message) values(time_stamp,type_id,raw_message);
    select into  last_message_id currval(pg_get_serial_sequence('message', 'id'));
    foreach x in ARRAY field_values
    LOOP
        insert into message_field_value(last_message_id,field_id,fieldValue) select left(x,strpos(x,',')-1), right(x,length(x)-strpos(x,','));
    END LOOP;
END
$$LANGUAGE plpgsql

It's called like this:

select neil_test('2001-01-01 08:00:00.1234','F','RAW',ARRAY['One,value1','two,value2','three,value3'])

This works OK but what I'd really like to do is use the array directly. Something like :

     select neil_test('2001-01-01 08:00:00.1234','F',
'RAW',ARRAY[['One', 'value1'],['two','value2'],['three','value3']])

...

 insert into message_field_value(last_message_id,fieldid, field value) 
select field_values[1], field_values[2]

I've tried the unnest function but this doesn't work as it seems to flatten out the whole array and I lose the pairs. Is something like this even possible with Postgres arrays?

alexpods · Accepted Answer · 2015-04-02 02:02:29Z

0

Use SLICE in the FOREACH statement to iterate over elements of multidimencional array. Without SLICE it iterates over individual elements of the array. This works for me:

CREATE OR REPLACE FUNCTION neil_test2(time_stamp timestamp with time zone, type_id text, raw_message text,field_values text[][2])
RETURNS void 
AS $$
DECLARE
    last_message_id bigint;
    x TEXT[];
BEGIN
    INSERT INTO message(time_stamp,type_id,raw_message) VALUES (time_stamp,type_id,raw_message);
    SELECT INTO  last_message_id currval(pg_get_serial_sequence('message', 'id'));
    FOREACH x SLICE 1 in ARRAY field_values LOOP
        INSERT INTO message_field_value(last_message_id, field_id, field_value) SELECT last_message_id, x[1], x[2];
    END LOOP;
END
$$LANGUAGE plpgsql;

answered Apr 2, 2015 at 2:02

alexpods

48.7k10 gold badges104 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

Selecting from multidimensional array parameter in Postgres

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