Error in PostgreSQL function with dynamic query for update

Question

This is the error message:

ERROR:  error de sintaxis en o cerca de «;»
LINE 35: END;

I am trying to create a dynamic execution query to update the drug price for an individual product, for a supplier, or for a laboratory. This is my function in Postgres:

CREATE OR REPLACE FUNCTION mod_medicamento(char,text,char,float)RETURNS void as $$
Declare
/*
$1 for table (L=laboratorio,P=Proovedor(supplier),M=medicamento
$2 for name of lab,supplier or product
$3 paramater for tyoe of operation (A for increment price D for decrement price)
$4 for porcentage(0.01 to 0.99)
*/

tabla ALIAS FOR $1;
nombre ALIAS FOR $2;
operacion ALIAS FOR $3;
porcentaje ALIAS FOR $4;
nombre_tabla varchar(30);
nombre_campo varchar(30);
calculo varchar(20);
BEGIN

    if tabla='L'then
        nombre_tabla:='laboratorio';
        nombre_campo:='laboratorio';
    else if tabla='P'then
        nombre_tabla:='proovedor';
        nombre_campo:='proovedor';
    else if tabla='M' then
        nombre_tabla:='medicamento';
        nombre_campo:='nombre';
    else
        RAISE EXCEPTION 'selecciona una opcion valida (L/P/M)';
    END IF; 

    if operacion='A' then
        calculo:='precio+precio*';
     else if operacion='D' then
        calculo:='precio-precio*';
    else    
        RAISE EXCEPTION 'selecciona una opcion valida de operacion(A/D)';   
    end if;
    
    Execute format('Update medicamento set precio=%L%L where id_%L = (Select id_%L from %I
            where %I like %L)',calculo,porcentaje,nombre_tabla,nombre_tabla,nombre_tabla,nombre_campo,
                  nombre);
END;
$$ LANGUAGE plpgsql;

`else if` needs to be either `ELSIF` or `ELSEIF` per [Conditionals](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS). — Adrian Klaver, Jun 14 '23 at 19:26
oh god, that it's, now is running,thank you so much!! However latter i'll test the all function correctly. — Perez Monge Santiago, Jun 14 '23 at 19:36
[So do you have your answer?](https://meta.stackexchange.com/a/5235/169168) — Erwin Brandstetter, Jun 24 '23 at 03:58

score 0 · Answer 1 · answered Jun 15 '23 at 01:52

Adrian already pointed out the immediate syntax error.
But there is more. Consider this rewrite:

CREATE OR REPLACE FUNCTION mod_medicamento(_tabla char, _nombre text, _operacion char, _porcentaje float) -- ①
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
/*
$1 table (L=laboratorio, P=proovedor(supplier), M=medicamento
$2 name of lab, supplier, or product
$3 paramater for type of operation (A for increment price, D for decrement price)
$4 for percentage(0.01 to 0.99)
*/
   _nombre_tabla text;
   _nombre_campo text;
   _calculo text;
BEGIN
   -- enforce: $4 for percentage(0.01 to 0.99)  -- ②
   IF _porcentaje > 0 AND _porcentaje < 1 THEN  -- actually in range (0, 1)
      -- all good
   ELSE -- also catches null
      RAISE EXCEPTION '_porcentaje must be in range [0, 1). Was: %', _porcentaje;
   END IF;
   
   CASE _tabla  -- ③
   WHEN 'L' THEN
      _nombre_tabla := 'laboratorio';
      _nombre_campo := 'laboratorio';
   WHEN 'P' THEN
      _nombre_tabla := 'proovedor';
      _nombre_campo := 'proovedor';
   WHEN 'M' THEN
      _nombre_tabla := 'medicamento';
      _nombre_campo := 'nombre';
   ELSE
     RAISE EXCEPTION 'selecciona una opcion valida (L/P/M)';
   END CASE;

   CASE _operacion
   WHEN 'A' THEN
      _calculo := '+';
   WHEN 'D' THEN
      _calculo := '-';
   ELSE
     RAISE EXCEPTION 'selecciona una opcion valida de operacion(A/D)';
   END CASE;

   EXECUTE format(
-- RAISE NOTICE '%'  , format(  -- use instead of EXECUTE for debugging!
$q$
UPDATE medicamento m
SET    precio = m.precio * (1 %1$s %2$s)  -- ④, ⑤
FROM   %3$I t                             -- ⑥
WHERE  t.id_%4$s LIKE %5$L
AND    m.id_%3$s = t.id_%3$s
$q$
    , _calculo, _porcentaje, _nombre_tabla, _nombre_campo, _nombre);
END
$func$;

Generates and executes a query of the form:

UPDATE medicamento m
SET    precio = m.precio * (1 + 0.123)
FROM   proovedor t
WHERE  t.id_proovedor LIKE 'nombre'
AND    m.id_proovedor = t.id_proovedor

① Use of ALIAS is discouraged. Use named parameters instead.
I like to prefix parameter and variable names with an underscore (_) to avoid naming conflicts with column names, but that's optional.

② Actually enforce the allowed range for _porcentaje. (Also disallowing 0 which wouldn't change anything.)

③ A "switched" CASE is more efficient & elegant for the use case. Example:

Display column name with max value between several columns

④ Simplified computation. precio + precio * 0.123 --> precio * (1 + 0.123)

⑤ Your original concatenation did not work at all since the format specifier %L in format() adds single quotes that break the syntax.

⑥ Use a FROM clause in the UPDATE instead of a correlated subquery. Your original query raises an exception if more than one row matches. Also: faster.

Since you are not returning anything, you may want a PROCEDURE instead of the FUNCTION. But either works. See:

When to use stored procedure / user-defined function?

Dynamic SQL like this is potentially open to SQL injection. But while you don't concatenate user input directly, you are safe.

Error in PostgreSQL function with dynamic query for update

1 Answers1