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
– Clodoaldo Neto, Commented Sep 6, 2014 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
– William R, Commented Sep 6, 2014 at 10:17
So if app_rtc is already a date what's the point in casting it to a date? — user330315
– user330315, Commented Sep 6, 2014 at 10:21

Clodoaldo Neto · Accepted Answer · 2014-09-06 10:22:43Z

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 6, 2014 at 10:22

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres date-numeric not working in function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related