Re: Syntax help please

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 <fhevia(at)ip-tel(dot)com(dot)ar>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: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Ruben Gouveia
> Enviado el: Jueves, 04 de Septiembre de 2008 16:37
> Para: pgsql-sql(at)postgresql(dot)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;
>
>
>
>
>
>