sql - salida - procedure oracle ejemplos

Ejecutar sentencias SQL en PL/pgSQL solo si una fila no existe

Question

Quiero hacer algo como esto en una función PL / pgSQL en Postgres 9.6:

INSERT INTO table1 (id, value) VALUES (1, ''a'') ON CONFLICT DO NOTHING;
--- If the above statement didn''t insert a new row
---   because id of 1 already existed, 
---   then run the following statements

INSERT INTO table2 (table1_id, value) VALUES (1, ''a'');
UPDATE table3 set (table1_id, time) = (1, now());

Sin embargo, no sé cómo determinar si el primer INSERT realidad insertó una nueva fila, o si el hecho de ON CONFLICT DO NOTHING se hizo.

Podría hacer un SELECT al comienzo de la función para ver si existe un registro con id de 1 en la table1 antes de ejecutar todas las sentencias de SQL, pero creo que esto generaría condiciones de carrera.

Answer 1

La manera más fácil y confiable es con la variable especial ENCONTRADA, de esta manera:

INSERT INTO table1 (id, value) values (1, ‘a’) on conflict do nothing;

SI ENCONTRADO ENTONCES

--éxito

MÁS

--fracaso

TERMINARA SI;

Aquí está la documentación para diagnosticar una declaración https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html

Answer 2

Postgres tiene la cláusula de returning y los CTE para hacer lo que desee:

WITH t1 as (
      INSERT INTO table1 (id, value)
          VALUES (1, ''a'')
          ON CONFLICT DO NOTHING
          RETURNING *
     ),
     t2 as (
      INSERT INTO table2 (table1_id, value) 
          SELECT id, value 
          FROM (SELECT 1 as id, ''a'' as value) t
          WHERE NOT EXISTS (SELECT 1 FROM t1) 
   )
UPDATE table3
    set (table1_id, time) = (1, now())
    WHERE NOT EXISTS (SELECT 1 FROM t1);

La update parece extraña porque actualiza todas las filas en la table3 .

Answer 3

Tal vez quieres decir algo como esto?

INSERT INTO table1 (id, value) VALUES (1, ''a'') ON CONFLICT DO NOTHING;
--- If the above statement didn''t insert a new row
---   because id of 1 already existed, 
---   then run the following statements

affected_rows := SQL%ROWCOUNT;

IF affected_rows = 0 THEN
    INSERT INTO table2 (table1_id, value) VALUES (1, ''a'');
    UPDATE table3 set (table1_id, time) = (1, now());
END IF

Answer 4

Para una función plpgsql, use la variable especial FOUND :

CREATE FUNCTION foo(int, text)
 RETURNS void AS
$$
BEGIN
   INSERT INTO table1 (id, value) VALUES ($1, $2) ON CONFLICT DO NOTHING;

   IF NOT FOUND THEN
      INSERT INTO table2 (table1_id, value) VALUES ($1, $2);
      UPDATE table3 set (table1_id, time) = ($1, now())
      WHERE  ????;  -- you surely don''t want to update all rows in table3
   END IF;
END
$$

Llamada:

SELECT foo(1, ''a'');

FOUND se establece en falso si INSERT no inserta ninguna fila.

El manual sobre la cláusula ON CONFLICT :

ON CONFLICT DO NOTHING simplemente evita insertar una fila como acción alternativa.

El manual sobre Obtención del estado del resultado

UPDATE INSERT UPDATE , INSERT y DELETE establecen FOUND verdadero si al menos una fila está afectada, es falso si no hay ninguna fila afectada.

Para que quede claro, esto ejecuta las declaraciones posteriores si una fila en la table1 ya existe, por lo que la nueva fila no se inserta. (Como lo solicitó, pero contrario al título de su pregunta).

Si solo desea verificar si existe una fila:

• Declaración IF de PostgreSQL

¿Condición de carrera?

Si los comandos subsiguientes en la misma transacción dependen de la fila existente en la table1 (con un FK por ejemplo), querrá bloquearlo para defenderse de las transacciones concurrentes que lo eliminen o actualicen mientras tanto. Una forma de hacerlo: en lugar de DO NOTHING use DO UPDATE , pero en realidad no actualice la fila. La fila sigue bloqueada:

INSERT INTO table1 AS t (id, value)
VALUES ($1, $2)
ON     CONFLICT (id) DO UPDATE  -- specify unique column(s) or constraint / index
SET    id = t.id WHERE FALSE;   -- never executed, but locks the row

Obviamente, si puede descartar transacciones concurrentes que podrían eliminar o actualizar la misma fila de manera conflictiva, entonces el problema no existe.

Explicación detallada:

• Cómo incluir filas excluidas en RETORNO desde INSERTAR ... EN CONFLICTO
• ¿SELECCIONAR o INSERTAR en una función es propenso a las condiciones de carrera?