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

Function Syntax involving pipes and ' marks?

From: "Ruben Gouveia" <rubes7202(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Function Syntax involving pipes and ' marks?
Date: 2008-09-09 23:20:40
Message-ID: 51e507b0809091620k91f5576w722d708b1043e8ed@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
No matter how many times i try, i can't seem to get the write amount of '
marks around the date parameters in my v_where declaration. What am i doing
wrong here?

v_stmt should look like this if done correctly:

select count(distinct m.id)
from (select id, greatest(max(last_p),max(last_b)) as date_created from job
group by id) m where m.jb_date < '2008-08-29' and m.jb >='2008-08-28'.
when instead it's coming out like this:
select count(distinct m.id) from (select id,
greatest(max(last_periodic),max(last_boot)) as date_created from mediaportal
group by id) m where m.date_created < 2008-08-29 and m.date_created
>=2008-08-28 .
...no tick marks around the dates.

here's my code:

CREATE OR REPLACE FUNCTION fcn_job(p_date date,
                                            p_type varchar,
                                            p_jobid numeric)
RETURNS numeric AS $$

DECLARE
    v_job numeric := 0;
    v_stmt varchar(1024);
    v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'||
                            ' and m.jb_date >='||p_date||'';


BEGIN
    v_stmt := fcn_gen_statement(p_type, v_where, p_newonly);
    execute v_stmt into v_job;
    RAISE NOTICE 'sql looks like this: % . ',v_stmt;
    return v_job;
END;
$$ LANGUAGE plpgsql;

Responses

pgsql-sql by date

Next:From: Alvaro HerreraDate: 2008-09-10 00:58:48
Subject: Re: Function Syntax involving pipes and ' marks?
Previous:From: Milen A. RadevDate: 2008-09-09 20:16:32
Subject: Re: How to provide password to pg_dump command ?

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