Unable to wrap a simple CTE in plpgsql loop

Question

For a test database, I would like to create a bunch of test users (all with "default" values). There is a table called users, and another called user_properties. For each test user, I need to make corresponding entries in both these tables. I already have a nicely working CTE to do this, but I am unable to wrap this CTE in a loop.

CREATE OR REPLACE FUNCTION ins() 
returns void as 
$BODY$
BEGIN
--RETURN record;
 FOR Loopid IN 0..10 LOOP
with 
 user as (
 insert into user
 values(default) 
 returning id
 )
 ,user_property as (
 insert into user_property (property_of) 
 select id from user 
 returning id
 )
select id from user_property;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

It creates the function, but on running it complains

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.

But it doesn't seem possible to replace the last select of the CTE with a perform instead - that's a syntax error. So how can I do this?

The table structures can be assumed to be very simple since most columns will take the default values. Table user has a single column id, and user_properties has two columns id and property_of - which is a foreign key to col id in the user table. Both the id columns are auto generated sequences.

The few questions (example) which have very similar titles have their answer that a loop/cte isn't really necessary, so they don't help me.

• ahron: At the moment, since I am unable to do the loop in pl/pgsql, I use a shell script loop to generate and run a sql file n times. It makes no difference to me, but is a terrible way of doing things. So I'd like to do it right :-)

Answer 1

First: user is a reserved keyword, you need to use double quotes if you want to use it as a table name: "user" But I strongly suggest you find a different name.

To answer your immediate question: just remove the final select from the CTE - that's the one cause the error (as you need to store the result of that select somewhere).

CREATE OR REPLACE FUNCTION ins() 
returns void as 
$BODY$
declare
 loopid integer;
BEGIN
 FOR Loopid IN 0..10 LOOP
 with new_user as (
 insert into "user"
 values(default) 
 returning id
 )
 insert into user_property (property_of) 
 select id 
 from new_user; 
 END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Note that the language name is an identifier, you should not put it in single quotes.

However, you don't really need a function for that:

with new_users as as (
 insert into "user" (id)
 select i 
 from generate_series(1,10) --<< this is the number of rows. 
 returning id
)
insert into user_property (property_of)
select id 
from new_users;