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

Escaping quotes and double-quotes problem

From: Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>
To: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Escaping quotes and double-quotes problem
Date: 2002-02-24 15:04:45
Message-ID: 200202241504.g1OF4jFP002350@www1.translationforge (view raw or flat)
Thread:
Lists: pgadmin-hackers
Dear Dave,

I discovered quote escape problems in pgAdmin2: 

1) View definition
In pgAdmin2, try create a view "abc_def" with definition SELECT 'abc' || 
'def'. If you alter the view several times it will grow like:

SELECT ''abc'' || ''def'',
SELECT '''abc''' || '''def''',
SELECT ''''abc'''' || ''''def'''',
SELECT '''''abc''''' || '''''def'''''.
None of these queries work.

Single and double quotes should not be escaped.
CREATE VIEW "abc" AS SELECT 'abc' works
CREATE VIEW "abc" AS SELECT ''abc'' does not.

This is because definition is stored using OIDs and special mechanisms.

2) Functions source (PLpgSQL)
var := 'abc' || 'def' works
var := ''abc'' || ''def'' works
var := '''abc''' || '''def''' doesn't

Quotes and double quotes should be escaped using \.
single quote -> \'
double quote -> \"

There is no need to replace ' with '' and so on. This is because function 
source is stored as text in PostgreSQL.

3) In both cases, dbSZ seems to be buggy.
I fixed the problem for function source. Not for view definition. Are there 
cases in views where we should escape some characters? What is your opinion?

Cheers,
Jean-Michel

pgadmin-hackers by date

Next:From: Jean-Michel POUREDate: 2002-02-24 15:40:26
Subject: Re: Schema unique stamp (OID, MD5?)
Previous:From: Dave PageDate: 2002-02-24 08:52:20
Subject: Re: Schema unique stamp (OID, MD5?)

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