Geek Answers Handbook

PostgreSQL PERFORM Function

I have this function in my database:

CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"(nombrearticulo character varying, descripcion text, idtipo integer, idfamilia bigint, artstock integer, minstock integer, maxstock integer, idmarca bigint, precio real, marcastock integer) RETURNS boolean AS $BODY$ DECLARE articulo "Articulo"%ROWTYPE; BEGIN SELECT * INTO articulo FROM "Articulo" WHERE "Nombre" = $1 AND "idTipo"=$3 AND "idFamilia"=$4; IF NOT FOUND THEN INSERT INTO "Articulo" ("Nombre", "Descripcion", "idTipo", "idFamilia", "Stock", "MinStock", "MaxStock") Values ($1, $2, $3, $4, $5, $6, $7); SELECT last_value INTO articulo."idArticulo" FROM "public"."Articulo_idArticulo_seq"; END IF; SELECT * FROM "ArticuloMarca" AS am WHERE am."idArticulo" = articulo."idArticulo" and am."idMarca" = $8; IF NOT FOUND THEN Insert into "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock") Values (articulo."idArticulo", $8, $9, $10); RETURN TRUE; END IF; RETURN FALSE; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION "insertarNuevoArticulo"(character varying, text, integer, bigint, integer, integer, integer, bigint, real, integer) OWNER TO postgres;

But as soon as I try to use it, it says that I need to use PERFORM if I want to discard the results! The problem is that I do not want to! I want them in the declared line articulo !

I use this statement:

 SELECT "insertarNuevoArticulo"('Acetaminofen', 'caro', '1' , '1', '8', '1', '10', '1', '150.7', '10');

And the error I get is 42601, syntax error! How could this be if I use the IDE to create it! Any idea on the problem?

+8
function postgresql
source share
2 answers

@Glenn is already correctly diagnosed that SELECT without a target is causing an error.

But you obviously do not want SELECT INTO , you just want to set the FOUND status. Use PERFORM for this.

Consider this correspondence of your function:

 CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"(nombrearticulo text, descripcion text, idtipo integer, idfamilia bigint, artstock integer, minstock integer, maxstock integer, idmarca bigint, precio real, marcastock integer) RETURNS boolean AS $BODY$ DECLARE articulo "Articulo"%ROWTYPE; BEGIN SELECT * INTO articulo FROM "Articulo" WHERE ("Nombre", "idTipo", "idFamilia") = ($1, $3, $4); IF NOT FOUND THEN INSERT INTO "Articulo" ("Nombre", "Descripcion", "idTipo", "idFamilia", "Stock", "MinStock", "MaxStock") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "idArticulo" INTO articulo."idArticulo"; END IF; PERFORM * FROM "ArticuloMarca" WHERE "idArticulo" = articulo."idArticulo" AND "idMarca" = $8; IF NOT FOUND THEN INSERT INTO "ArticuloMarca"("idArticulo", "idMarca", "PrecioReferencial", "Stock") VALUES (articulo."idArticulo", $8, $9, $10); RETURN TRUE; END IF; RETURN FALSE; END; $BODY$ LANGUAGE plpgsql;

Another important point:

Aside: if you wouldn’t use mixed case identifiers like you, you won’t need to quote all these ugly double names.

Better but

 IF EXISTS ... IF NOT EXISTS ...

As explained in this answer back:
PL / pgSQL checks if a row exists - SELECT INTO boolean

+12
source share

This line looks suspicious to me and probably makes you sad:

 SELECT * FROM "ArticuloMarca" AS am WHERE am."idArticulo" = articulo."idArticulo" and am."idMarca" = $8;

You perform SELECT in your function, but do nothing with the results. You need to execute SELECT INTO, as before, in your function.

+4
source share

More articles:


All Articles