invalid input syntax for type postgres function

Question

I have this Function:

CREATE OR REPLACE FUNCTION year_2014.insertsub
(
  accountid      integer,
  analyseid      integer,
  analysedate    date,
  requestnumber  integer
)
RETURNS integer AS
$$
DECLARE r integer;
begin
FOR r IN SELECT public."SubAnalyze".id FROM  public."SubAnalyze" where public."SubAnalyze".analyze_id = analyseid
loop
   INSERT INTO year_2014."Results"(account_id, analyze_id, "date", subanalyze_id, requestnumber)
VALUES
    (accountid, analyseid, 'analysedate', r, requestnumber);
END LOOP;
RETURN 1; 
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION year_2014.insertsub(accountid integer, analyseid integer, analysedate date, requestnumber integer)
  OWNER TO postgres;

when I run the function error message

SQL Error: ERROR: invalid input syntax for type date: "analysedate" LINE 3: (accountid, analyseid, 'analysedate', r, requestnumber) ^ QUERY: INSERT INTO year_2014."Results"(account_id, analyze_id, "date", subanalyze_id, requestnumber) VALUES (accountid, analyseid, 'analysedate', r, requestnumber) CONTEXT: PL/pgSQL function year_2014.insertsub(integer,integer,date,integer) line 6 at SQL statement

AndreaBoc AndreaBoc · Accepted Answer · 2014-07-11T13:12:01

Don't quote 'analysedate', in this way you send the text analysedate to your insert command instead of a date, the correct syntax is:

INSERT INTO year_2014."Results"(account_id, analyze_id, "date", subanalyze_id, requestnumber)
VALUES
(accountid, analyseid, analysedate, r, requestnumber);

invalid input syntax for type postgres function

1 Answers