I can't believe myself. It always comes down to some missing semicolon. Thanks for your help. I am embarrassed.
On Thu, Sep 4, 2008 at 12:49 PM, Fernando Hevia <[EMAIL PROTECTED]>wrote: > 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Ruben Gouveia > Enviado el: Jueves, 04 de Septiembre de 2008 16:37 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] Syntax help please > > > I can't for the life of me figure out what's wrong with this syntax. > I get the following error when i try and create this function. > > ERROR: syntax error at or near "$2" at character 15 > QUERY: SELECT $1 $2 := $3 || $4 || $5 > CONTEXT: SQL statement in PL/PgSQL function "fcn_gen_statement" > near line 24 > > here's what i am trying to create: > > CREATE OR REPLACE FUNCTION fcn_gen_statement(p_type varchar, > p_where varchar, > p_newonly numeric) > RETURNS varchar AS $$ > > 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; > > > > > >
