CODEWITHSUNDEEP
sqlpostgresql
Anand Srinivasan
Anand Srinivasan

Reputation: 470

Postgres FOR loop syntax error at or near

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?

Upvotes: 0

Views: 665

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions