"Sugandha Shah" <Sugandhas@cybage.com> writes:
> CREATE FUNCTION del_old_history() RETURNS bool AS '
> declare
> var_history_age_limit int4;
> set_time datetime;
> BEGIN
> select into var_history_age_limit history_age_limit from database_info;
> IF (var_history_age_limit is not null) THEN
> set_time := select current_date()+ INTERVAL ' ' $var_history_age_limit day' ';
You don't use a $ to refer to plpgsql variables (except for parameters,
and there the *name* of the parameter is actually $n). Also ":= select"
is redundant; I believe the correct syntax would be
set_time := current_date() + ...
or at least it would be if SQL expected current_date to come with
parentheses, but it doesn't, so the next bug is that you need
set_time := current_date + ...
Another problem is that "INTERVAL ''var_history_age_limit day''" isn't
going to work because plpgsql doesn't do textual interpolation of
variables into queries. (If you want a language where that's how it's
done, try pltcl or plperl.) While you could hack around with something
like "CAST(text(var_history_age_limit) || ' day' as interval)", this
actually is very much the hard way to do it --- adding an integer to a
date already does what you want. So this statement should just be
set_time := current_date + var_history_age_limit;
although given the logic used later I wonder whether what you are after
isn't really
set_time := current_date - var_history_age_limit;
BTW I'd declare set_time as timestamp or timestamptz if I were you;
datetime is an obsolete datatype name that's not going to be accepted
anymore as of 7.3.
regards, tom lane
