Postgres FOR loop syntax error at or near

Question

I'm trying to write rows into an array from table_a and table_b and insert it into a table. This is my function def:

CREATE OR REPLACE FUNCTION format()
RETURNS void
LANGUAGE 'sql'
VOLATILE
COST 100
AS $BODY$ 

select array(select row(wk1,wk2) from table_a ) into arr1;
select array(select row(wk1,wk2) from table_b ) into arr2;

FOR i IN 1..2 LOOP
    insert into table_c values(arr1[i],arr2[i]);
END LOOP;

$BODY$;

I'm getting an error in the for loop line stating "syntax error at or near FOR". According to the official documentation, that is correct. What am I missing here?

user330315 · Accepted Answer · 2019-08-19 17:42:55Z

1

You can only use a FOR loop in functions (or procedures) using language plpgsql. SQL has no for loops.

But you don't need a loop or a function for this at all. This can be done using a simple INSERT statement with a SELECT statement as its source:

insert into table_c
select wk1, wk2 from table_a
union all
select wk1, wk2 from table_b;

answered Aug 19, 2019 at 17:42

user330315

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

Collectives™ on Stack Overflow

Postgres FOR loop syntax error at or near

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