From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Davidson, Robert <robdavid@amazon.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using a parameter in Interval
Date: Tue, 21 Mar 2006 17:03:02 -0800 (PST)
Message-ID: <20060321165750.J85509@megazone.bigpanda.com> (raw)
In-Reply-To: <8333C841129E074E9F83FC80676BA76E0BEBEF@exchg-sea3-03.ant.amazon.com>
References: <8333C841129E074E9F83FC80676BA76E0BEBEF@exchg-sea3-03.ant.amazon.com>
On Tue, 21 Mar 2006, Davidson, Robert wrote:
> No matter how I try to concatenate, I can't seem to get a parameter to be used by INTERVAL in a function:
>
> CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$
> BEGIN
> RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) || ' weeks');
> END;
> $$ LANGUAGE plpgsql;
>
> --select * from testing(1);
>
> ERROR: syntax error at or near "CAST" at character 34
> QUERY: SELECT current_date - INTERVAL (CAST( $1 AS varchar) || ' weeks')
> CONTEXT: SQL statement in PL/PgSQL function "testing" near line 2
>
> I have tried concatenating it as a declared variable (with and without apostrophes)
> 1 weeks
> And
> '1 weeks'
>
> With no success. Any tips?
You'd need a cast, not INTERVAL foo as the latter is for interval literals
(and CAST... is not a valid interval literal even if the output of the
concatenation looks like an interval literal). I'd go with the suggestion
of using int * interval instead of concatenation in any case.
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: sszabo@megazone.bigpanda.com, robdavid@amazon.com
Subject: Re: Using a parameter in Interval
In-Reply-To: <20060321165750.J85509@megazone.bigpanda.com>
* 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