Postgres FOR LOOP

by

Procedural elements like loops are not part of the SQL language and can only be used inside the body of a procedural language function, procedure (Postgres 11 or later) or a DO statement, where such additional elements are defined by the respective procedural language. The default is PL/pgSQL, but there are others.

Example with plpgsql:

DO
$do$
BEGIN 
   FOR i IN 1..25 LOOP
      INSERT INTO playtime.meta_random_sample
         (col_i, col_id)                       -- declare target columns!
      SELECT  i,     id
      FROM   tbl
      ORDER  BY random()
      LIMIT  15000;
   END LOOP;
END
$do$;

For many tasks that can be solved with a loop, there is a shorter and faster set-based solution around the corner. Pure SQL equivalent for your example:

INSERT INTO playtime.meta_random_sample (col_i, col_id)
SELECT t.*
FROM   generate_series(1,25) i
CROSS  JOIN LATERAL (
   SELECT i, id
   FROM   tbl
   ORDER  BY random()
   LIMIT  15000
   ) t;

About generate_series():

About optimizing performance of random selections:

More Related Contents:

  1. How to set value of composite variable field using dynamic SQL
  2. Function to loop through and select data from multiple tables
  3. Loop through columns of RECORD
  4. What are ‘$$’ used for in PL/pgSQL
  5. INSERT with dynamic table name in trigger function
  6. The forgotten assignment operator “=” and the commonplace “:=”
  7. Pass multiple values in single parameter
  8. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  9. Loop over array dimension in plpgsql
  10. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  11. How do I do large non-blocking updates in PostgreSQL?
  12. Execute a dynamic crosstab query
  13. Is there a way to disable function overloading in Postgres
  14. Can I make a plpgsql function return an integer without using a variable?
  15. PostgreSQL function returning multiple result sets
  16. Dynamic SQL (EXECUTE) as condition for IF statement
  17. Return setof record (virtual table) from function
  18. Returning from a function with OUT parameter
  19. SELECT raises exception in PL/pgSQL function
  20. Test for null in function with varying parameters
  21. Return SETOF rows from PostgreSQL function
  22. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  23. Dynamically generate columns for crosstab in PostgreSQL
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. ERROR: unterminated quoted string at or near
  26. How can I send some http request from postgresql function or trigger
  27. Select from a table variable
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment