0

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.

  • 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 :-) – Yogesch Mar 26 at 5:45
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.