<div dir="ltr">I can't for the life of me figure out what's wrong with this syntax. I get the following error when i
tryand create this function.<br /><br /><i style="color: rgb(255, 0, 0);">ERROR: syntax error at or near "$2" at
character15<br /> QUERY: SELECT $1 $2 := $3 || $4 || $5 <br />CONTEXT: SQL statement in PL/PgSQL function
"fcn_gen_statement"near line 24</i><br style="color: rgb(255, 0, 0);" /><br />here's what i am trying to create:<br
/><br/>CREATE OR REPLACE FUNCTION fcn_gen_statement(p_type varchar, <br />
p_wherevarchar,<br /> p_newonly numeric)<br />RETURNS varchar AS $$<br
/><br/>DECLARE<br /> c_select varchar(64) := 'select count(distinct <a href="http://m.id">m.id</a>) ';<br />
c_fromvarchar(64) := 'from job m ';<br /> c_newonly_from varchar(128) := 'from (select id, min(date_created) as
date_created'||<br /> 'from hr '||<br
/> 'group_by id) m ';<br /> v_from varchar(512);<br /> v_where
varchar(512);<br/> v_stmt varchar(2048); <br /><br />BEGIN<br /> if p_newonly =
1then<br /> v_from := c_newonly_from;<br /> else<br /> v_from := c_from;<br /> end if;<br /><br
/> if upper(p_type) = 'NEW' then<br /> v_stmt := c_select || v_from || p_where;<br /> elsif upper(p_type)
='OLD' then<br /> v_from := c_from ;<br /> v_where := p_where<br /> v_stmt := c_select || v_from
||v_where;<br /> elsif upper(p_type) = 'LAST_JOB' then<br /> v_from := v_from || <br /> ',
(selectdistinct job_id ' ||<br /> 'from job_log' ||<br /> 'where status = 10) d ';<br />
v_where:= p_where ||<br /> 'and <a href="http://m.id">m.id</a> = d.job_id ';<br /> v_stmt :=
c_select|| v_from || v_where;<br /> elsif upper(p_type) = 'NEW_JOB' then<br /> v_from := v_from ||<br
/> ', (select distinct job_id ' ||<br /> 'from job_log' ||<br /> 'where status = 12) d
';<br/> v_where := p_where ||<br /> 'and <a href="http://m.id">m.id</a> = d.job_id ';<br />
v_stmt:= c_select || v_from || v_where;<br /><br /> end if;<br /> return (v_stmt);<br />END;<br />$$ LANGUAGE
plpgsql;<br/></div>
