Re: Function Syntax involving pipes and ' marks? - Mailing list pgsql-sql

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;