0

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;
2
  • 1
    else if needs to be either ELSIF or ELSEIF per Conditionals. Commented Jun 14, 2023 at 19:26
  • oh god, that it's, now is running,thank you so much!! However latter i'll test the all function correctly. Commented Jun 14, 2023 at 19:36

1 Answer 1

0

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:

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

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

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.