Обсуждение: Function Syntax involving pipes and ' marks?
No matter how many times i try, i can't seem to get the write amount of ' marks around the date parameters in my v_where declaration. What am i doing wrong here?
v_stmt should look like this if done correctly:
select count(distinct m.id)
from (select id, greatest(max(last_p),max(last_b)) as date_created from job group by id) m where m.jb_date < '2008-08-29' and m.jb >='2008-08-28'.
when instead it's coming out like this:
select count(distinct m.id) from (select id, greatest(max(last_periodic),max(last_boot)) as date_created from mediaportal group by id) m where m.date_created < 2008-08-29 and m.date_created >=2008-08-28 .
...no tick marks around the dates.
here's my code:
CREATE OR REPLACE FUNCTION fcn_job(p_date date,
p_type varchar,
p_jobid numeric)
RETURNS numeric AS $$
DECLARE
v_job numeric := 0;
v_stmt varchar(1024);
v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'||
' and m.jb_date >='||p_date||'';
BEGIN
v_stmt := fcn_gen_statement(p_type, v_where, p_newonly);
execute v_stmt into v_job;
RAISE NOTICE 'sql looks like this: % . ',v_stmt;
return v_job;
END;
$$ LANGUAGE plpgsql;
v_stmt should look like this if done correctly:
select count(distinct m.id)
from (select id, greatest(max(last_p),max(last_b)) as date_created from job group by id) m where m.jb_date < '2008-08-29' and m.jb >='2008-08-28'.
when instead it's coming out like this:
select count(distinct m.id) from (select id, greatest(max(last_periodic),max(last_boot)) as date_created from mediaportal group by id) m where m.date_created < 2008-08-29 and m.date_created >=2008-08-28 .
...no tick marks around the dates.
here's my code:
CREATE OR REPLACE FUNCTION fcn_job(p_date date,
p_type varchar,
p_jobid numeric)
RETURNS numeric AS $$
DECLARE
v_job numeric := 0;
v_stmt varchar(1024);
v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'||
' and m.jb_date >='||p_date||'';
BEGIN
v_stmt := fcn_gen_statement(p_type, v_where, p_newonly);
execute v_stmt into v_job;
RAISE NOTICE 'sql looks like this: % . ',v_stmt;
return v_job;
END;
$$ LANGUAGE plpgsql;
Ruben Gouveia escribió:
> No matter how many times i try, i can't seem to get the write amount of '
> marks around the date parameters in my v_where declaration. What am i doing
> wrong here?
Apparently you're not aware that you can nest the $$ quote marks. You
could just use $a$ to assign to the varchar, and use ' inside that
string to get the literal '. Of course, you could use also $b$ instead
of a plain single quote.
That is, v_where varchar(256) := $a$ where m.jb_date < $a$||p_date + integer '1'|| $a$
andm.jb_date >='$a$ ||p_date|| $a$'$a$;
or (harder to read)
v_where varchar(256) := $a$ where m.jb_date < $a$||p_date + integer '1'|| $a$ and
m.jb_date>=$b$ $a$ ||p_date|| $a$ $b$ $a$;
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Is that more expensive to run than just useing a bunch of ticks? Sent from Apple iPhone 3G On Sep 10, 2008, at 11:24 AM, Bricklen Anderson <banderson@presinet.com> wrote: > Ruben Gouveia wrote: >> v_where varchar(256) := 'where m.jb_date < '||p_date + integer >> '1'|| >> ' and m.jb_date >='||p_date||''; > > Try wrapping your p_date in a quote_literal like > ... > 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... > > eg. > > CREATE OR REPLACE FUNCTION fcn_job(p_date date, p_type > varchar,p_jobid numeric) RETURNS numeric AS $$ > DECLARE > v_job numeric := 0; > v_stmt varchar(1024); > v_where varchar(256) := 'where m.jb_date < '|| > quote_literal(p_date + integer '1')||' and m.jb_date >='|| > quote_literal(p_date); > BEGIN > v_stmt := fcn_gen_statement(p_type, v_where, p_newonly); > execute v_stmt into v_job; > RAISE NOTICE 'sql looks like this: % . ',v_stmt; > return v_job; > END; > $$ LANGUAGE plpgsql;
Ruben Gouveia wrote: > v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'|| > ' and m.jb_date >='||p_date||''; Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... eg. CREATE OR REPLACE FUNCTION fcn_job(p_date date, p_type varchar,p_jobid numeric) RETURNS numeric AS $$ DECLARE v_job numeric := 0; v_stmt varchar(1024); v_where varchar(256) := 'where m.jb_date < '||quote_literal(p_date + integer '1')||' and m.jb_date >='||quote_literal(p_date); BEGIN v_stmt := fcn_gen_statement(p_type, v_where, p_newonly); execute v_stmt into v_job; RAISE NOTICE 'sql lookslike this: % . ',v_stmt; return v_job; END; $$ LANGUAGE plpgsql;
Ruben Gouveia wrote: > Is that more expensive to run than just useing a bunch of ticks? >> Try wrapping your p_date in a quote_literal like >> ... >> 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... I personally have never noticed any increased overhead from quote_literal.
Bricklen Anderson <banderson@presinet.com> writes:
> Ruben Gouveia wrote:
>> Is that more expensive to run than just useing a bunch of ticks?
> I personally have never noticed any increased overhead from quote_literal.
Much more important is that you'll reliably get the right answer.
regards, tom lane
i will try that. thank you
On Wed, Sep 10, 2008 at 11:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bricklen Anderson <banderson@presinet.com> writes:
> Ruben Gouveia wrote:
>> Is that more expensive to run than just useing a bunch of ticks?> I personally have never noticed any increased overhead from quote_literal.Much more important is that you'll reliably get the right answer.
regards, tom lane