tutorial salida recorrer procedimientos procedimiento parametros espaƱol ejemplos ejecutar developer cursores con almacenados almacenado sql postgresql stored-procedures plpgsql sql-insert

sql - salida - procedure oracle ejemplos



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

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.


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


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 .


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


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:

¿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: