Error in creating function in postgres

Question

I have this function:

CREATE OR REPLACE FUNCTION public.sp_rptadvsalincr(
 p_flag character,
 p_empcds character varying,
 p_incrtype character varying)
 RETURNS SETOF "TABLE(empcd character, name character varying, basic integer, incrdt timestamp without time zone, incrdbasic integer, nextincrdt timestamp without time zone, deptgenno character varying)"
 LANGUAGE 'plpgsql'
 COST 100.0
 VOLATILE NOT LEAKPROOF 
 ROWS 1000.0
AS $function$

When executed, it shows this error:

ERROR: type "TABLE(empcd character, name character varying, basic integer, i" does not exist
NOTICE: identifier "TABLE(empcd character, name character varying, basic integer, incrdt timestamp without time zone, incrdbasic integer, nextincrdt timestamp without time zone, deptgenno character varying)" will be truncated to "TABLE(empcd character, name character varying, basic integer, i"

Why do I get this error, and how can I fix it?

• user330315: Apart from what Laurenz wrote, you are also missing the function body.
• Łukasz Kamiński: You also need to remove setof because RETURNS TABLE implies set and PostgreSQL will return error if you attempt to mix those.
• Erwin Brandstetter: What you show is not a function, only the header with a starting dollar quote, the rest obviously truncated. Please always present a complete (minimal) function - the one you actually have (minus possibly irrelevant stuff in the body). And always your version of Postgres.

Answer 1

It's either:

RETURNS TABLE (...)

or:

RETURNS SETOF sometype

You have an illegal mix of both forms. Looks like you replaced a double-quoted type name with a custom table definition. This would work:

CREATE OR REPLACE FUNCTION public.sp_rptadvsalincr(
 p_flag character,
 p_empcds character varying,
 p_incrtype character varying)
 RETURNS TABLE(empcd character, name character varying, basic integer, incrdt timestamp without time zone, incrdbasic integer, nextincrdt timestamp without time zone, deptgenno character varying)
 LANGUAGE plpgsql
 COST 100
 VOLATILE NOT LEAKPROOF 
 ROWS 1000
AS $function$
BEGIN
RETURN QUERY 
SELECT NULL::char, NULL::varchar, NULL::int, NULL::timestamp, NULL::int, NULL::timestamp, NULL::varchar;
END
$function$;

Call:

SELECT * FROM pg_temp.sp_rptadvsalincr('a','b','c');

Details in the manual.

Answer 2

Remove the double quotes around TABLE(...).