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
A 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 $MY_FUNKY_LOOP$
.
BEGIN...END
BEGIN
and 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 LOOP
The 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 counter
variable.
RAISE NOTICE
This is the console.log
of Postgres :)
EXECUTE $$ $$ USING
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.
So as counter
increases, the item name will increment as well.
Why even use this?
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.
Top comments (1)
How about this?
SQL works much much better with set based operators than classic loops.
Obviously for 100 rows it won't make a noticeable difference 🙂