Re: Few Queries

"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