PostgreSQL SQL - Quote

Author

Quote_literal()

James M Doherty

2005-11-30, 8:24 pm

declare
SQL text;
NSQL text;

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

NSQL := quote_literal(SQL);

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

Jim

James M Doherty
Principal
<file://C:\Documents and Settings\jmd\Applica
tion
Data\Microsoft\Signa
tures\odoherty_crest
.jpg>
JMD CONSULTING
411 Thunderbay Dr
Georgetown, TX 78626
A reputation is something others give you HONOR is something you give
yourself!

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2005-11-30, 8:24 pm

"James M Doherty" <jim@jdoherty.net> writes:
> declare
> SQL text;
> NSQL text;

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

> NSQL := quote_literal(SQL);[
/color]
[color=darkred]
> 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'# NSQL text;
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 4: Have you searched our list archives?

http://archives.postgresql.org

James M Doherty

2005-12-03, 9:24 am

Tom,

I found the problem. It seems that a function I was calling
date_add_days(date,i
nt)
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:tgl@sss.pgh.pa.us]
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" <jim@jdoherty.net> writes:
> declare
> SQL text;
> NSQL text;

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

> NSQL := quote_literal(SQL);[
/color]
[color=darkred]
> 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'# NSQL text;
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

Sponsored Links