Create Store Procedure in PostgreSQL [duplicate]

Question

Create Store Procedure in PostgreSQL [duplicate]

Navigation

#1 by (0 votes)

0

I want to create a store procedure in PostgreSQL but I see that it always has a return, what I need is to make a simple LIKE I do not need to return anything, this is my query has syntax errors for example it is missing an AS after the name of the store, can I set a @idarticle to the parameters?

CREATE FUNCTION uspSelectlist(varchar, varchar) 
idarticulo varchar(6);
 descripcion varchar(40);
BEGIN
 SELECT IdArticulo, Descripcion FROM tblarticulos
                    WHERE (@idarticulo IS NULL OR idarticulo ILIKE @idarticulo)
                    AND (@descripcion IS NULL OR descripcion ILIKE @descripcion)
END;
' LANGUAGE 'plpgsql';

I need to build that store.

postgresql postgresql-9.3

asked by Pedro Ávila 11.01.2017 в 21:47

source

1 answer

output query php CSV in a column Url with period "." in Django Rest Framework

score 0 · Accepted Answer

In postgre both functions and stored procedures are declared as functions, with the exception that the latter return void

Something like this:

CREATE OR REPLACE FUNCTION add_city(city VARCHAR(70), state CHAR(2)) 
RETURNS void AS $$
BEGIN
  INSERT INTO cities VALUES (city, state);
END;
$$ LANGUAGE plpgsql;

In your case:

CREATE OR REPLACE FUNCTION uspSelectlist(idarticulo varchar(70), descripcion varchar(70)) 
RETURNS void AS $$
BEGIN
SELECT IdArticulo, Descripcion FROM tblarticulos
                WHERE (idarticulo  IS NULL OR idarticulo LIKE idarticulo)
                AND (descripcion  IS NULL OR descripcion LIKE descripcion);
END;
$$ LANGUAGE plpgsql;