Postgres FOR LOOP

Question

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the tool I have. This is what I have so far:

for i in 1..25 LOOP
   insert into playtime.meta_random_sample
   select i, ID
   from   tbl
   order  by random() limit 15000
end loop

Answer 1

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():

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

About optimizing performance of random selections:

• Best way to select random rows PostgreSQL

Answer 2

Below is example you can use:

create temp table test2 (
  id1  numeric,
  id2  numeric,
  id3  numeric,
  id4  numeric,
  id5  numeric,
  id6  numeric,
  id7  numeric,
  id8  numeric,
  id9  numeric,
  id10 numeric) 
with (oids = false);

do
$do$
declare
     i int;
begin
for  i in 1..100000
loop
    insert into test2  values (random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random());
end loop;
end;
$do$;

Answer 3

I just ran into this question and, while it is old, I figured I'd add an answer for the archives. The OP asked about for loops, but their goal was to gather a random sample of rows from the table. For that task, Postgres 9.5+ offers the TABLESAMPLE clause on WHERE. Here's a good rundown:

https://www.2ndquadrant.com/en/blog/tablesample-in-postgresql-9-5-2/

I tend to use Bernoulli as it's row-based rather than page-based, but the original question is about a specific row count. For that, there's a built-in extension:

https://www.postgresql.org/docs/current/tsm-system-rows.html

CREATE EXTENSION tsm_system_rows;

Then you can grab whatever number of rows you want:

select * from playtime tablesample system_rows (15);

Answer 4

I find it more convenient to make a connection using a procedural programming language (like Python) and do these types of queries.

import psycopg2
connection_psql = psycopg2.connect( user="admin_user"
                                  , password="***"
                                  , port="5432"
                                  , database="myDB"
                                  , host="[ENDPOINT]")
cursor_psql = connection_psql.cursor()

myList = [...]
for item in myList:
  cursor_psql.execute('''
    -- The query goes here
  ''')

connection_psql.commit()
cursor_psql.close()

Answer 5

Here is the one complex postgres function involving UUID Array, For loop, Case condition and Enum data update. This function parses each row and checks for the condition and updates the individual row.

CREATE OR REPLACE FUNCTION order_status_update() RETURNS void AS $$
DECLARE
  oid_list uuid[];
  oid uuid;
BEGIN
  SELECT array_agg(order_id) FROM order INTO oid_list;
  FOREACH uid IN ARRAY uid_list
  LOOP
    WITH status_cmp AS (select COUNT(sku)=0 AS empty, 
                    COUNT(sku)<COUNT(sku_order_id) AS partial, 
                    COUNT(sku)=COUNT(sku_order_id) AS full 
                    FROM fulfillment 
                    WHERE order_id=oid)
    UPDATE order
    SET status=CASE WHEN status_cmp.empty THEN 'EMPTY'::orderstatus
    WHEN status_cmp.full THEN 'FULL'::orderstatus
    WHEN status_cmp.partial THEN 'PARTIAL'::orderstatus
    ELSE null
    END 
    FROM status_cmp
    WHERE order_id=uid;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

To run the above function

SELECT order_status_update();

Answer 6

Using procedure.

CREATE or replace PROCEDURE pg_temp_3.insert_data()
LANGUAGE SQL
BEGIN ATOMIC
INSERT INTO meta_random_sample(col_serial, parent_id)
SELECT t.*
FROM   generate_series(1,25) i
CROSS  JOIN LATERAL (
   SELECT i, parent_id
   FROM    parent_tree order by random() limit 2
   ) t;
END;

Call the procedure.

call pg_temp_3.insert_data();

PostgreSQL manual: https://www.postgresql.org/docs/current/sql-createprocedure.html