Re: Syntax help please

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;