Forum OpenACS Q&A: next_day for PG 7.2

Posted by Andrei Popov on
I was trying to modify next_day(timestamptz,varchar) function to make it work on 7.1 and 7.2. I've tried a few approaches but they all seem to fail. Most recent looks thusly:

		next_day__somedate alias for $1;
		next_day__weekday  alias for $2;
		v_dow              integer;
		v_ref_dow		        integer;
		v_add_days		       integer;
		v_add_interval     varchar;
		v_pg_version       varchar;
		v_rz               timestamptz;
	-- I cant find a function that converts days of the week to
	-- the corresponding integer value, so I roll my own (above)
	-- We avoid extract(dow from timestamp) because of incompatible output
with to_char.
	v_ref_dow := dow_to_int(next_day__weekday);
	v_dow := to_number(to_char(next_day__somedate,'D'),'9');
	-- If next_day__weekday is the same day of the week as
	-- next_day__somedate, we add a full week.
	if v_dow < v_ref_dow
	     v_add_days := v_ref_dow - v_dow;
	     v_add_days := v_ref_dow - v_dow + 7;
	end if;

   select version() into v_pg_version;

   -- Do date month, but consider PG changes
	if v_pg_version ~ ' 7.2' then
		v_add_interval := v_add_days || ' days';
		v_rz := next_day__somedate + interval v_add_interval;
		v_rz := next_day__somedate + to_interval(v_add_days,'days');
	end if;

	return v_rz;

And I get
openacs-dev=# select next_day('3-JUL-2002'::date,'Sunday');
NOTICE:  Error occurred while executing PL/pgSQL function next_day
NOTICE:  line 31 at assignment
ERROR:  parser: parse error at or near "$2"
When I try to execute it. Any idea what's failing?
Posted by Don Baccus on
Since PL/pgSQL function bodies are strings you need to double up your apostrophes.  ''7.2'' not '7.2.', for instance.

This is a common error and there's talk in PG land of changing the parser to take an unquoted string followed by a unique delimiter much as is done in Oracle.  But that lies in the future ...

Posted by Andrei Popov on
Don, sorry for not mentioning this -- it's just a copy paste from pgaccess which, IIRC, handles apostrophies when doing a save. Besides, if I put a dummy v_rz := now(); instead of v_rz := next_day__somedate + interval v_add_interval; it all works (and so does v_rz := next_day__somedate;). There is something wrong with interval part.