Re: Few Queries

"Sugandha Shah" <Sugandhas(at)cybage(dot)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