IF with Extending SQL in PostgreSQL Stored Procedure

Question

IF with Extending SQL in PostgreSQL Stored Procedure

Navigation

#1 by (0 votes)

0

I try to do this in postgres

CREATE OR REPLACE FUNCTION public.busqueda_productos(_parametro character 
varying,_descripcion character varying,_estado character varying)
 RETURNS SETOF productos AS
 $BODY$
IF ($1 ='General' )then 
  SELECT * FROM productos WHERE descripcion ilike _descripcion||'%';
ELSIF ($1 ='Activo' )then 
SELECT * FROM productos WHERE descripcion ilike _descripcion||'%' and 
estado=_estado;
 END IF;
$BODY$
LANGUAGE sql

And I get this error:

ERROR: syntax error at or near "IF" LINE 4: IF ($ 1 = 'General') then ^

Error

ERROR: syntax error at or near "IF" SQL state: 42601 Character: 180

sql postgresql

asked by Victor Rigoberto José Santiago 17.12.2017 в 21:32

source

1 answer

How to connect to sql anywhere 9 sybase from android? Error with the average query CSS3 does not adapt?

score 0 · Answer 1

You can not use IF ... ELSEIF ... END IF; in a function defined as language SQL , because this type of conditions is part of the language PL/pgSQL , not SQL .

So you have at least 2 options. Choose the one that seems most appropriate.

1. Express conditions in pure SQL

If you want to define the function using only SQL, then instead of using conditions with IF ... ELSIF ... END IF; , this can be expressed in an SQL query using conditions AND and OR :

CREATE OR REPLACE FUNCTION public.busqueda_productos(
    _parametro character varying,
    _descripcion character varying,
    _estado character varying)
 RETURNS SETOF productos AS
 $BODY$
   select *
     from productos
    where (_parametro = 'General' and descripcion ilike _descripcion||'%')
      or  (_parametro = 'Activo' and descripcion ilike _descripcion||'%' and estado=_estado);
 $BODY$
LANGUAGE sql;

2. Define the function using the PL / pgSQL language

If you really want to use conditions to use one query or another depending on the value of _parametro , then you must use PL/pgSQL :

CREATE OR REPLACE FUNCTION public.busqueda_productos(
    _parametro character varying,
    _descripcion character varying,
    _estado character varying)
 RETURNS SETOF productos AS
 $BODY$
 begin
   if _parametro = 'General' then
      RETURN QUERY SELECT * FROM productos WHERE descripcion ilike _descripcion||'%';
   elsif _parametro = 'Activo' then
      RETURN QUERY SELECT * FROM productos WHERE descripcion ilike _descripcion||'%' and estado=_estado;
   end if;
 end;
$BODY$
LANGUAGE plpgsql;