PostgresSQL - Stored procedure

Question

PostgresSQL - Stored procedure

Navigation

#1 by (0 votes)

2

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;

You are 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

asked by anonymous 16.04.2015 / 20:50

1 answer

reCAPTCHA failed in file_get_contents Grouping by day and date period

score 0 · Answer 1

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

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