Skip site navigation (1) Skip section navigation (2)

Re: Syntax help please

From: "Ruben Gouveia" <rubes7202(at)gmail(dot)com>
To: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Syntax help please
Date: 2008-09-04 19:55:51
Message-ID: 51e507b0809041255h70c079d7iea76e1a71c837514@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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;
>
>
>
>
>
>

In response to

pgsql-sql by date

Next:From: Ruben GouveiaDate: 2008-09-04 20:41:16
Subject: Dynamic SQL Syntax help please
Previous:From: Fernando HeviaDate: 2008-09-04 19:49:20
Subject: Re: Syntax help please

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group