FOR loop condition inside PostgreSQL function

Question

Given below is my table structure

CREATE TABLE gtab86
(
  mlid integer DEFAULT nextval('seq_gtab86_id'::regclass),
  acyrid integer,
  lmonth integer,
  islocked boolean
)

In this table lmonth is the month fied, and acyrid is the year denoting value for example 1.
I wrote the following Function to insert into gtab86.

CREATE OR REPLACE FUNCTION createmonthlock(iacyrid integer) 
    RETURNS void AS '
BEGIN 
   INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,1); 
   INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,2);
   INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,3); 
   INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,4); 
   INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,5); 
   INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,6); 
   INSERT INTO gtab86(acyrid, lmonth)VALUES (iacyrid,7); 
   INSERT INTO gtab86(acyrid, lmonth)VALUES (iacyrid,8); 
   INSERT INTO gtab86(acyrid, lmonth)VALUES (iacyrid,9); 
   INSERT INTO gtab86(acyrid, lmonth)VALUES (iacyrid,10); 
   INSERT INTO gtab86(acyrid, lmonth)VALUES (iacyrid,11); 
   INSERT INTO gtab86(acyrid, lmonth)VALUES (iacyrid,12);
END;' 
LANGUAGE plpgsql VOLATILE COST 100;

As you can see, insert into is repeated 12 times (for 12 lmonth field rows).
How to make it only one insert query?
Is it possible to use for 0 to 12 kind things inside a function?

user330315 · Accepted Answer · 2014-07-01 11:58:28Z

3

You can use insert .. select based on generate_series().

You also don't need PL/pgSQL for this, a plain SQL function will do:

create or replace function createmonthlock(iacyrid integer) 
 returns void
as
$body$
   INSERT INTO gtab86(acyrid, lmonth) 
   select iacyrid, num
   from generate_series(1,12) num;
$body$
language sql;

answered Jul 1, 2014 at 11:58

user330315

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

1 Comment

Clodoaldo Neto Over a year ago

Exactly what I was going to answer. You just missed the returns void bit.

paul · Accepted Answer · 2014-07-01 11:57:22Z

0

You can insert multiple rows with a single INSERT statement:

INSERT INTO gtab86(acyrid, lmonth) VALUES (iacyrid,1), (iacyrid,2), (iacyrid,3), (iacyrid,4), (iacyrid,5), (iacyrid,6), (iacyrid,7), (iacyrid,8), (iacyrid,9), (iacyrid,10), (iacyrid,11), (iacyrid,12);

answered Jul 1, 2014 at 11:57

paul

22.1k1 gold badge56 silver badges54 bronze badges

Collectives™ on Stack Overflow

FOR loop condition inside PostgreSQL function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related