I don't know about you but I often need a 100 of something in my database to properly test a page. If my UI isn't developed yet to handle that action or the count of something is too large, that's when I turn to SQL.
Today, for instance, I needed to test virtual scrolling and I didn't have enough data locally to trigger that behavior so I wrote a quick loop in Postgres to generate a 100 items:
DO $FN$ BEGIN FOR counter IN 1..100 LOOP RAISE NOTICE 'Counter: %', counter; EXECUTE $$ INSERT INTO items(name, active) VALUES ('Test item ' || $1, true) RETURNING id $$ USING counter; END LOOP; END; $FN$
If you've followed me for a while, you'll recognize this loop from the wild SQL query I had to write for work. However, this loops is much simpler. And this item would generate 100 items in my database with sequentially numbered names.
Let's go through this query real quick
DO block creates a special block that can execute some SQL. It's start and end is denoted by
$FN$ in my query but you can use any delimiter that starts and ends with a dollar sign. So
$DO$ works, as does
END create the section of the loop which runs the actual SQL statement. You can also have a
DECLARE section which lets us assign and create some variables. We don't need it here.
FOR counter IN 1..100 LOOP lets us create a for loop. Postgres has a handy way of creating ranges via the
.. syntax (so
1..100 is 1 through 100). The current value of the range in the iteration is saved in the
This is the
console.log of Postgres :)
EXECUTE will literally just execute the SQL between
$$. What EXECUTE lets us do is specify
USING which is used for variable substitution.
I like to think of this as a little self-executing function where
USING lets us specify the parameters and execute runs the SQL and substitutes our parameter in the statement.
counter increases, the item name will increment as well.
It's just a super handy small snippet for generating data. 🤷
What's cool is that the more I learn SQL, the more I find myself using these types of loops and logic and adding onto them. There's nothing stopping us from using some sort of random generator to create better varied data for development or for generating data in a staging environment.