PLPGSQL Array in Functions

Question

How do I use an array to insert to a table in a function?

CREATE TABLE places
(
id      SERIAL PRIMARY KEY
,location   VARCHAR(100) NOT NULL
,timestamp  TIMESTAMP
,person_id  INTEGER NOT NULL REFERENCES people ON UPDATE...
);

CREATE TABLE people
(
id      SERIAL PRIMARY KEY
,name   VARCHAR(100) NOT NULL
);

I am trying to do a function where I would do:

SELECT function(location_name, now(), '{1,2,3}');

This would INSERT 3 records into the places table for each person_id in the array, with the same location and timestamp. The main problem is that the length of the array can be dynamic, so the function should be able to handle:

SELECT function(location_name, now(), '{3,5,7,8,10}');

I don't know where to start in making the INSERT dynamic and extracting the ids from the array. Unest can be used to INSERT INTO variables in DECLARE but the dynamic aspect cannot be done. I hope I made this clear.

Erwin Brandstetter · Accepted Answer · 2012-06-23 17:17:43Z

2

Writing a plpgsql function is always a possibility (and I do it a lot). But this case can be solved simpler:

INSERT INTO places (location, ts, person_id) 
SELECT 'some_location', now(), unnest('{1,2,3}'::int4[])

I replaced your column name timestamp with ts because I don't think it is a good idea to use a type name as identifier. It leads to confusing side effects and error messages.

If you need to JOIN to the result set of an unnest() function - like your question seems to imply (but the problem turned out to be simpler) - you have to make it a sub-query:

INSERT INTO places (location, ts, name)
SELECT 'some_location', now(), p.name
FROM  (SELECT unnest('{1,2,3}'::int4[]) AS id) AS x
JOIN   people p USING (id);

I added the column name, which is not in your model, just do demonstrate.

edited Jun 23, 2012 at 17:17

answered Mar 8, 2012 at 18:25

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Neeran Over a year ago

Thank you for your answer, can I do, FOR i in array LOOP instead? Are there any side effects to LOOP?

Erwin Brandstetter Over a year ago

Inside a plpgsql function or a DO statement, yes. No idea, what side effects that could be. You might be interested in the new FOREACH loop

Collectives™ on Stack Overflow

PLPGSQL Array in Functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related