Here's one I used to convert an int to an interval in another project: CREATE OR REPLACE FUNCTION to_interval (sec INTEGER) RETURNS INTERVAL AS $$ BEGIN RETURN (sec || ' seconds')::INTERVAL; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
select to_interval (5); to_interval ------------- 00:00:05 (1 row) You should be able to replace ' seconds' with ' weeks' just fine. Excuse the outlook-ism, -Owen -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Davidson, Robert Sent: Tuesday, March 21, 2006 4:53 PM To: pgsql-sql@postgresql.org Subject: [SQL] Using a parameter in Interval 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? Many thanks, Robert ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
