Re: [SQL] Quote_literal()

[James M Doherty]

Tom,

I found the problem. It seems that a function I was calling
date_add_days(date,int)
Returned for some reason a text date + [some unprintable garbage] two
characters worth
This in turn caused the quote_literal() to fail by returning only the
initial single quote.

I resolved the problem by replacing the date_add_days() function with
date_pli() [comes with 8.1],
After doing this my problem disappeared. I am still investigating what the
cause of of the
Failure in date_add_days() was [part of my 'c' library that I ported from
gnumeric. I suspect
A memory leak [lack of pfree] in the one of the functions.

Any way thanks for taking the time to look at my issue.

Jim 


James M Doherty
Principal

JMD CONSULTING
411 Thunderbay Dr
Georgetown, TX 78626
A reputation is something others give you HONOR is something you give
yourself!

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 30, 2005 4:28 PM
To: James M Doherty
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Quote_literal() 

"James M Doherty" <[EMAIL PROTECTED]> writes:
>  declare 
>   SQL text;
>   NSQLtext;

> SQL := ''10/3/2009'';

> NSQL := quote_literal(SQL);

> The result is '10/3/2009   with no closing quote.

Works for me:

regression=# create function foo() returns text as '
regression'# declare SQL text;
regression'# NSQLtext;
regression'# begin
regression'#   SQL := ''10/3/2009'';
regression'#   NSQL := quote_literal(SQL);
regression'#   return NSQL;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo();
 foo
-
 '10/3/2009'
(1 row)


Again, I suggest giving a *complete* example, because you are obviously not
looking in the right place for your problem.

regards, tom lane



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] question using 'between' in a sql query

[Theodore Petrosky]

My table

create table mytable (
employeecode text,
info text,
somestuff text,
insertdate timestamp with time zone);

I have an interface where the user can query for all
employeecode between two dates. I assumed from the
docs that:

SELECT employeecode FROM mytable WHERE insertdate
BETWEEN '11/20/2005' AND '11/20/2005'

would yield an inclusive result set. I am being lazy
and I didn't want to check if the user was looking for
just one day (it looks like I will have to not be
lazy).

Is this correct behavior? if 'between' is inclusive of
the start and end dates why doesn't this query result
in all the rows on '11/20/2005'?

BTW this is 8.1 on OS X 10.4.3

Ted



__ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] question using 'between' in a sql query

[Michael Fuhr]

On Sat, Dec 03, 2005 at 09:54:16AM -0800, Theodore Petrosky wrote:
> if 'between' is inclusive of the start and end dates why doesn't
> this query result in all the rows on '11/20/2005'?

EXPLAIN shows what happens when you compare a date against a timestamp:

Filter: ((insertdate >= '2005-11-20 00:00:00-08'::timestamp with time zone)
 AND (insertdate <= '2005-11-20 00:00:00-08'::timestamp with time zone))

One way around this is to cast the timestamp column to date:

SELECT employeecode
FROM mytable
WHERE insertdate::date BETWEEN '11/20/2005' AND '11/20/2005';

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org