In case it is a version issue: This should always work regardless of version: Just cast the $1 variable as text followed by interval:
create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - ($1::text||'days')::interval; return next a; return; end $$ language 'plpgsql'; -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Milen A. Radev Sent: Tuesday, December 19, 2006 2:54 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Help with quotes in plpgsql Richard Ray написа: > On Tue, 19 Dec 2006, Milen A. Radev wrote: > >> Richard Ray ÿÿÿÿÿÿÿÿÿÿÿÿ: >>> How should this be properly quoted >>> >>> create or replace function test(integer) returns setof text as $$ >>> declare >>> a record; >>> begin >>> select into a now() - interval '$1 day'; >>> return next a; >>> return; >>> end >>> $$ language 'plpgsql'; >>> >>> I'm not having a lot of luck >> >> >> Welcome to psql 8.2.0, the PostgreSQL interactive terminal. >> >> Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >> milen=> create or replace function test(integer) returns setof text as $$ >> milen$> declare >> milen$> a record; >> milen$> begin >> milen$> select into a now() - interval '$1 day'; >> milen$> return next a; >> milen$> return; >> milen$> end >> milen$> $$ language 'plpgsql'; >> CREATE FUNCTION >> milen=> >> >> >> >> No problems here. What version are you using? >> > > I'm using 8.1.0 but I don't think that's the problem > I have no problem creating the function but it will only substract 1 day Sorry about that - I have not understand your problem. In addition to the solution already proposed you could use "EXECUTE". See more info here - http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN . -- Milen A. Radev ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
