Postgresql – Unable to wrap a simple CTE in plpgsql loop

cteplpgsqlpostgresql

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.

Best Answer

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;
Related Question