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 NOTHINGsimplemente evita insertar una fila como acción alternativa.
El manual sobre Obtención del estado del resultado
UPDATEINSERTUPDATE,INSERTyDELETEestablecenFOUNDverdadero 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: