Re: Function Syntax involving pipes and ' marks?

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;