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

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;