Postgres FOR LOOP
Matthew Martinez 
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 loop6 Answers
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:
3Below 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$;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:
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:
CREATE EXTENSION tsm_system_rows;Then you can grab whatever number of rows you want:
select * from playtime tablesample system_rows (15);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()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();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:
