Thread: Function Syntax involving pipes and ' marks?

<div dir="ltr">No matter how many times i try, i can't seem to get the write amount of ' marks around the date
parametersin my v_where declaration. What am i doing wrong here?<br /><br />v_stmt should look like this if done
correctly:<br/><br />select count(distinct <a href="http://m.id">m.id</a>) <br /><span style="color: rgb(204, 0,
0);">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'. </span><br /> when instead it's coming out like this:<br /><span style="color:
rgb(204,0, 0);">select count(distinct <a href="http://m.id">m.id</a>) 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-29and m.date_created >=2008-08-28 . </span><br /> ...no tick marks around the dates.<br /><br />here's my
code:<br/><br />CREATE OR REPLACE FUNCTION fcn_job(p_date date, <br />                                           
p_typevarchar,<br />                                            p_jobid numeric)<br /> RETURNS numeric AS $$<br /><br
/>DECLARE<br/>    v_job numeric := 0;<br />    v_stmt varchar(1024);<br />   <span style="color: rgb(204, 0, 0);">
v_wherevarchar(256) := 'where m.jb_date < '||p_date + integer '1'||</span><br style="color: rgb(204, 0, 0);" /><span
style="color:rgb(204, 0, 0);">                            ' and m.jb_date >='||p_date||'';</span><br
/>                                 <br /><br />BEGIN<br />    v_stmt := fcn_gen_statement(p_type, v_where,
p_newonly);<br/>     execute v_stmt into v_job;<br />    RAISE NOTICE 'sql looks like this: % . ',v_stmt;<br />   
returnv_job;<br />END;<br />$$ LANGUAGE plpgsql;<br /><br /><br /></div> 

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

<div dir="ltr">i will try that. thank you<br /><br /><div class="gmail_quote">On Wed, Sep 10, 2008 at 11:45 AM, Tom
Lane<span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><divclass="Ih2E3d">Bricklen Anderson <<a href="mailto:banderson@presinet.com">banderson@presinet.com</a>>
writes:<br/> > Ruben Gouveia wrote:<br /> >> Is that more expensive to run than just useing a bunch of
ticks?<br/><br /></div><div class="Ih2E3d">> I personally have never noticed any increased overhead from
quote_literal.<br/><br /></div>Much more important is that you'll reliably get the right answer.<br /><br />          
            regards, tom lane<br /></blockquote></div><br /></div>