Postgressql - Stored Procedure

Asked

Viewed 92 times

3

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;

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 search parameter.

sql postgresql

  • Where the parameter comes from?

1 answer

1

You should call your function with all the information you request as parameters. In the case of Smt parameter use simple quotes since its function is requesting a varchar.

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

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.