Thread: Syntax help please

<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> 

You seem to be missing a ';' in this line:       v_from := c_from ;       v_where := p_where            <--- missing ;
here      v_stmt := c_select || v_from || v_where;
 

Regards,
Fernando



________________________________
De: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] En nombre de Ruben GouveiaEnviado el: Jueves, 04 de Septiembre de 2008
16:37Para:pgsql-sql@postgresql.orgAsunto: [SQL] Syntax help pleaseI can't for the life of me figure out what's wrong
withthis syntax.
 
I get the following error when i try and create this function.ERROR:  syntax error at or near "$2" at character
15QUERY: SELECT   $1   $2  :=  $3  ||  $4  ||  $5 CONTEXT:  SQL statement in PL/PgSQL function "fcn_gen_statement"
 
near line 24here's what i am trying to create:CREATE OR REPLACE FUNCTION fcn_gen_statement(p_type varchar,
                               p_where varchar,                                             p_newonly numeric)RETURNS
varcharAS $$DECLARE    c_select varchar(64) := 'select count(distinct m.id) ';    c_from varchar(64) := 'from job m ';
 c_newonly_from varchar(128) := 'from (select id,
 
min(date_created) as date_created '||                                         'from hr '||
          'group_by id) m ';    v_from varchar(512);    v_where varchar(512);    v_stmt varchar(2048);
               BEGIN    if p_newonly = 1 then        v_from := c_newonly_from;    else        v_from := c_from;    end
if;   if upper(p_type) = 'NEW' then        v_stmt := c_select || v_from || p_where;    elsif upper(p_type) = 'OLD' then
      v_from := c_from ;        v_where := p_where        v_stmt := c_select || v_from || v_where;      elsif
upper(p_type)= 'LAST_JOB' then        v_from := v_from ||             ', (select distinct job_id ' ||            'from
job_log'||            'where status = 10) d ';        v_where := p_where ||            'and m.id = d.job_id ';
v_stmt:= c_select || v_from || v_where;    elsif upper(p_type) = 'NEW_JOB' then        v_from := v_from ||
',(select distinct job_id ' ||            'from job_log' ||            'where status = 12) d ';        v_where :=
p_where||            'and m.id = d.job_id ';        v_stmt := c_select || v_from || v_where;    end if;    return
(v_stmt);END;$$LANGUAGE plpgsql;