Postgres date-numeric not working in function

Question

Can anyone help me to resolve the issue ??

    CREATE OR REPLACE FUNCTION func_maj_get_new_user(in_date numeric)
      RETURNS integer AS
    $BODY$
    declare

    count_newusr integer;

    begin


    SELECT count(a.app_uid) 
      INTO count_newusr 
    FROM 
    (SELECT s_start.app_uid 
       FROM devdba.s_maj_sdk_bs s_start 
      WHERE s_start.app_rts::date = current_date - in_date
       AND (s_start.app_uid,s_start.app_key) NOT IN(SELECT app_uid,app_key 
                                                      FROM datemp.maj_usr_mstr)
     )a;

return count_newusr;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;

The below function throws an error like ,

ERROR: operator does not exist: date - text LINE 1: ..._start WHERE s_start.app_rts::date = current_date - $1 AND...

The code shown in the error message does not exist in the posted function. Fix it — Clodoaldo Neto, Sep 06 '14 at 10:12
Sorry Code is app_rtc = current_date - in_date ,Thanks for Quick reply... app_rts is date column , I want to substract current_date - In_date(numeric argument) and check against app_rtc date column .. if i do it in normal query it works but in function its not working — William R, Sep 06 '14 at 10:17
So if `app_rtc` is already a `date` what's the point in casting it to a `date`? — , Sep 06 '14 at 10:21

score 1 · Accepted Answer · answered Sep 06 '14 at 10:22

1

in_date must be integer

current_date - in_date::integer

Or just pass it as integer

func_maj_get_new_user(in_date integer)

answered Sep 06 '14 at 10:22

Clodoaldo Neto

118,695
26
233
260

Postgres date-numeric not working in function

1 Answers1