PostgresSQL – Stored procedure

Question:

What's wrong with this Stored Procedure?

CREATE OR REPLACE FUNCTION public.sp_teste
(
  IN           varchar,
  OUT  codigo  integer,
  OUT  setor   varchar,
  OUT  grupo   integer
)
RETURNS SETOF record AS
$$
begin   
  return query SELECT codigo, setor, grupo FROM vw_setor WHERE vw_setor.setor LIKE $1;   
end;
$$
LANGUAGE 'plpgsql'
COST 100;

ALTER FUNCTION public.sp_teste(varchar)
  OWNER TO postgres;

It is returning this error:

SQL Error: ERROR:  column "smt" does not exist
LINE 1: SELECT * FROM public.sp_teste(smt) LIMIT 1000 OFFSET 0
                                      ^

Note: "SMT" is the parameter for searching.

Answer:

You must call your function with all the information you request as parameters. In the case of the smt parameter use single quotes since your function is requesting a varchar.

SELECT * FROM public.sp_teste('smt') LIMIT 1000 OFFSET 0
Scroll to Top