Postgres BOUCLE FOR

Question

Je tente d'obtenir 25 échantillons aléatoires de 15 000 identifiants d'une table. Au lieu d'appuyer manuellement sur exécuter à chaque fois, j'essaie de faire une boucle. Ce qui n'est pas l'utilisation optimale de Postgres, mais c'est l'outil que j'ai. Voici ce que j'ai fait jusqu'à présent :

pour i de 1 à 25 LOOP
   insérer dans playtime.meta_random_sample
   sélectionnez i, ID
   de     tbl
   ordre  par random() limiter 15000
boucle terminée

Answer 1

Les éléments procéduraux comme les boucles ne font pas partie du langage SQL et ne peuvent être utilisés que dans le corps d'une fonction de langage procédural, d'une procédure (Postgres 11 ou ultérieur) ou d'une instruction DO, où de tels éléments supplémentaires sont définis par le langage procédural respectif. Par défaut, c'est du PL/pgSQL, mais il en existe d'autres.

Exemple avec plpgsql :

DO
$do$
BEGIN 
   FOR i IN 1..25 LOOP
      INSERT INTO playtime.meta_random_sample
         (col_i, col_id)                       -- déclarer les colonnes cibles !
      SELECT  i,     id
      FROM   tbl
      ORDER  BY random()
      LIMIT  15000;
   END LOOP;
END
$do$;

Pour de nombreuses tâches qui peuvent être résolues avec une boucle, il y a une solution basée sur des ensembles plus courte et plus rapide juste à côté. Équivalent SQL pur pour votre exemple :

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;

À propos de generate_series() :

• Quel est le comportement attendu pour plusieurs fonctions renvoyant un ensemble dans la clause SELECT?

À propos de l'optimisation des performances des sélections aléatoires :

• Meilleure façon de sélectionner des lignes aléatoires PostgreSQL

Answer 2

Voici un exemple que vous pouvez utiliser :

créer table temporaire test2 (
  id1  numerique,
  id2  numerique,
  id3  numerique,
  id4  numerique,
  id5  numerique,
  id6  numerique,
  id7  numerique,
  id8  numerique,
  id9  numerique,
  id10 numerique) 
avec (oids = false);

faire
$faire$
declare
     i int;
begin
pour  i in 1..100000
boucle
    insérer dans test2  valeurs (random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random());
fin boucle;
fin;
$faire$;

Answer 3

Je suis tombé sur cette question et, bien qu'elle soit ancienne, j'ai pensé ajouter une réponse pour les archives. L'OP a demandé des boucles for, mais son objectif était de rassembler un échantillon aléatoire de lignes de la table. Pour cette tâche, Postgres 9.5+ propose la clause TABLESAMPLE sur WHERE. Voici un bon résumé :

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

Je tends à utiliser Bernoulli car il est basé sur les lignes plutôt que sur les pages, mais la question originale porte sur un nombre de lignes spécifique. Pour cela, il existe une extension intégrée :

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

CREATE EXTENSION tsm_system_rows;

Ensuite, vous pouvez obtenir le nombre de lignes souhaité :

select * from playtime tablesample system_rows (15);

Answer 4

Je trouve plus pratique de faire une connexion en utilisant un langage de programmation procédural (comme Python) et d'effectuer ce type de requêtes.

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('''
    -- La requête va ici
  ''')

connection_psql.commit()
cursor_psql.close()

Answer 5

Voici une fonction complexe postgres impliquant UUID, Array, boucle For, condition Case et mise à jour de données Enum. Cette fonction analyse chaque ligne, vérifie la condition et met à jour la ligne individuelle.

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)

`Pour exécuter la fonction ci-dessus

SELECT order_status_update();`