SELECT or PERFORM in a PL/pgSQL function
In plpgsql code, SELECT
without a target triggers an error. But you obviously do not want SELECT INTO
, you just want to set the status of FOUND
. You would use PERFORM
for that.
Better, yet, use IF EXISTS ...
. Consider this rewrite of your function:
CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"( nombrearticulo text, descripcion text, idtipo int, idfamilia bigint, artstock int, minstock int, maxstock int, idmarca bigint, precio real, marcastock int) RETURNS boolean LANGUAGE plpgsql AS$func$DECLARE _id_articulo "Articulo"."idArticulo"%TYPE;BEGIN SELECT a."idArticulo" INTO _id_articulo FROM "Articulo" a WHERE a."Nombre" = $1 AND a."idTipo" = $3 AND a."idFamilia" = $4; IF NOT FOUND THEN INSERT INTO "Articulo"("Nombre", "Descripcion", "idTipo", "idFamilia", "Stock", "MinStock", "MaxStock") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "Articulo"."idArticulo" INTO _id_articulo; END IF; IF EXISTS (SELECT FROM "ArticuloMarca" a WHERE a."idArticulo" = _id_articulo AND a."idMarca" = $8) THEN RETURN false; ELSE INSERT INTO "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock") VALUES (_id_articulo, $8, $9, $10); RETURN true; END IF;END$func$;
About EXISTS
:
The other major point:
- Use the
RETURNING
clause of theINSERT
statement instead of an additionalSELECT
.
Postgres 9.5+
In Postgres 9.5 or later use INSERT ... ON CONFLICT DO NOTHING
(a.k.a. "UPSERT") instead.
You would have UNIQUE
constraints on "Articulo"("Nombre", "idTipo", "idFamilia")
and "ArticuloMarca"("idArticulo", "idMarca")
and then:
CREATE OR REPLACE FUNCTION insert_new_articulo( nombrearticulo text, descripcion text, idtipo int, idfamilia bigint, artstock int, minstock int, maxstock int, idmarca bigint, precio real, marcastock int) RETURNS boolean LANGUAGE plpgsql AS$func$DECLARE _id_articulo "Articulo"."idArticulo"%TYPE;BEGIN LOOP SELECT "idArticulo" INTO _id_articulo FROM "Articulo" WHERE "Nombre" = $1 AND "idTipo" = $3 AND "idFamilia" = $4; EXIT WHEN FOUND; INSERT INTO "Articulo"("Nombre", "Descripcion", "idTipo", "idFamilia", "Stock", "MinStock", "MaxStock") VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT (tag) DO NOTHING RETURNING "idArticulo" INTO _id_articulo; EXIT WHEN FOUND; END LOOP; LOOP INSERT INTO "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock") VALUES (_id_articulo, $8, $9, $10) ON CONFLICT ("idArticulo", "idMarca") DO NOTHING; IF FOUND THEN RETURN true; END IF; IF EXISTS (SELECT FROM "ArticuloMarca" WHERE "idArticulo" = _id_articulo AND "idMarca" = $8) THEN RETURN false; END IF; END LOOP;END$func$;
This is faster, simpler and more reliable. The added loops rule out any remaining race conditions with concurrent writes (while adding hardly any cost). Without concurrent writes, you can simplify. Detailed explanation:
- Is SELECT or INSERT in a function prone to race conditions?
- How to use RETURNING with ON CONFLICT in PostgreSQL?
Aside: use legal, lower-case identifiers to avoid all the noisy double-quotes.
This line looks suspicious to me and is probably what is causing your grief:
SELECT * FROM "ArticuloMarca" AS am WHERE am."idArticulo" = articulo."idArticulo" and am."idMarca" = $8;
You are executing a SELECT within your function, but not doing anything with the results. You need to perform a SELECT INTO like you did earlier in your function.