Re: [GENERAL] Newbie question about escaping in a function
Try using EXECUTE. http://www.postgresql.org/docs/7.4/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Oct 26, 2004, at 11:57 AM, Naeem Bari wrote: I have a simple function defined thusly: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS ' DECLARE tdat timestamp; rdat timestamp; BEGIN IF ($1 IS NULL) THEN TDAT := NOW(); ELSE TDAT := $1; END IF; select tdat + interval ''$2 $3'' into rdat; return rdat; END; ' LANGUAGE 'plpgsql' VOLATILE; The problem is the interval part. How do I tell the bugger to use the second and third params as input to interval? I have tried different ways of escaping, from \’$2 $3\’ to ‘’$2 $3’’ and everything else in between, it just doesn’t like it. Help! J Thanks, naeem ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie question about escaping in a function
Thanks! Now I get it... naeem -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 3:05 PM To: Naeem Bari Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Newbie question about escaping in a function On Tue, 2004-10-26 at 11:57 -0500, Naeem Bari wrote: > I have a simple function defined thusly: > > > > CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, > varchar) > > RETURNS timestamp AS > > ' > > DECLARE > > tdat timestamp; > > rdat timestamp; > > BEGIN > > IF ($1 IS NULL) THEN > > TDAT := NOW(); > > ELSE > > TDAT := $1; > > END IF; It's neater to use the COALESCE() function, which is designed expressly for this. > select tdat + interval ''$2 $3'' into rdat; In PL/pgSQL that should be "select into rdat ..."; but that won't work in any case because you can't use passed parameters inside a string like that. > return rdat; > > END; > > ' > > LANGUAGE 'plpgsql' VOLATILE; > > > > The problem is the interval part. How do I tell the bugger to use the > second and third params as input to interval? I have tried different > ways of escaping, from \'$2 $3\' to ''$2 $3'' and everything else in > between, it just doesn't like it. You have to construct a command string and use EXECUTE: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS 'DECLARE tdatTIMESTAMP; result RECORD; cmd TEXT; BEGIN tdat := COALESCE($1, NOW()); cmd := ''SELECT '' || quote_literal(tdat) || ''::TIMESTAMP + INTERVAL '' || quote_literal($2 || '' '' || $3) || '' AS x''; FOR result IN EXECUTE cmd LOOP return result.x; END LOOP; END; ' LANGUAGE 'plpgsql' VOLATILE; -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie question about escaping in a function
On Tue, 2004-10-26 at 11:57 -0500, Naeem Bari wrote: > I have a simple function defined thusly: > > > > CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, > varchar) > > RETURNS timestamp AS > > ' > > DECLARE > > tdat timestamp; > > rdat timestamp; > > BEGIN > > IF ($1 IS NULL) THEN > > TDAT := NOW(); > > ELSE > > TDAT := $1; > > END IF; It's neater to use the COALESCE() function, which is designed expressly for this. > select tdat + interval ''$2 $3'' into rdat; In PL/pgSQL that should be "select into rdat ..."; but that won't work in any case because you can't use passed parameters inside a string like that. > return rdat; > > END; > > ' > > LANGUAGE 'plpgsql' VOLATILE; > > > > The problem is the interval part. How do I tell the bugger to use the > second and third params as input to interval? I have tried different > ways of escaping, from \’$2 $3\’ to ‘’$2 $3’’ and everything else in > between, it just doesn’t like it. You have to construct a command string and use EXECUTE: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS 'DECLARE tdatTIMESTAMP; result RECORD; cmd TEXT; BEGIN tdat := COALESCE($1, NOW()); cmd := ''SELECT '' || quote_literal(tdat) || ''::TIMESTAMP + INTERVAL '' || quote_literal($2 || '' '' || $3) || '' AS x''; FOR result IN EXECUTE cmd LOOP return result.x; END LOOP; END; ' LANGUAGE 'plpgsql' VOLATILE; -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Newbie question about escaping in a function
I have a simple function defined thusly: CREATE OR REPLACE FUNCTION datemath(timestamp with time zone, int4, varchar) RETURNS timestamp AS ' DECLARE tdat timestamp; rdat timestamp; BEGIN IF ($1 IS NULL) THEN TDAT := NOW(); ELSE TDAT := $1; END IF; select tdat + interval ''$2 $3'' into rdat; return rdat; END; ' LANGUAGE 'plpgsql' VOLATILE; The problem is the interval part. How do I tell the bugger to use the second and third params as input to interval? I have tried different ways of escaping, from \’$2 $3\’ to ‘’$2 $3’’ and everything else in between, it just doesn’t like it. Help! J Thanks, naeem
