From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sugandha Shah <Sugandhas@cybage.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Few Queries
Date: Wed, 14 Aug 2002 10:21:32 -0400
Message-ID: <7915.1029334892@sss.pgh.pa.us> (raw)
In-Reply-To: <01b301c24352$12570fb0$2005a8c0@cybage.com>
References: <01b301c24352$12570fb0$2005a8c0@cybage.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, Sugandhas@cybage.com
Subject: Re: Few Queries
In-Reply-To: <7915.1029334892@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox