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