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
user_properties has two columns
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.