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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq