CODEWITHSUNDEEP
sqlpostgresqlplpgsql
rina
rina

Reputation: 127

Error in creating function in postgres

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?

Upvotes: 1

Views: 621

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659167

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.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 248165

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

Upvotes: 0

Related Questions