Re: Escaping quotes and double-quotes problem

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr]
> Sent: 24 February 2002 15:05
> To: Dave Page
> Cc: pgadmin-hackers(at)postgresql(dot)org
> Subject: Escaping quotes and double-quotes problem
>
>
> 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 does not happen on my system. Have you broken something on yours? ;-)

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

No, this doesn't always work. I've experimented with this extensively in the
past. here's a quick example:

helpdesk=# create table "Fred's \"Stuff\" Table" (id int4);
ERROR: parser: parse error at or near "stuff"
ERROR: parser: parse error at or near "stuff"
helpdesk=# create table "Fred's ""Stuff"" Table" (id int4);
CREATE
helpdesk=# \d
List of relations
Name | Type | Owner
--------------------------------+----------+----------
Fred's "Stuff" Table | table | postgres

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

There is a need to do this with functions, because when you define them, the
quotes are doubled up because they function source is also within single
quotes. When PostgreSQL stores the source in pg_function.prosrc, it strips
out the additional quotes, so when we reconstruct the original SQL we must
put them back.

Having said that, the function update code doesn't do that (Add does) so
I've comitted a fix for that. *** Please remove your fix ***, it's not in
the right place and will probably cause excessive quoting in some cases.

> 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?

As I said, views are fine here.

Cheers, Dave.