Re: pgAdmin isn't quoting quotes

Stephen Denne wrote:
> (1.8RC1)
>
> If for some unusual reason I want to use pgadmin to add a column named:
>
> one "two" three
>
> Then I am able to specify this identifier as
>
> "one ""two"" three"
>
> but pgadmin will always show it as
>
> one "two" three
>
> Which I think is ok, but it shows it in generated SQL as
>
> "one "two" three"
>
> instead of
>
> "one ""two"" three"
>
> So trying to change the column's type (for example) attempts this SQL:
>
> ALTER TABLE dbversion ALTER "one "two" three" TYPE bigint;
> ALTER TABLE dbversion ALTER COLUMN "one "two" three" SET STATISTICS -1;
>
> If I alter the name again when changing the type, I get this instead:
>
> ALTER TABLE dbversion RENAME "one "two" three"  TO ""one ""two"" three"";
> ALTER TABLE dbversion ALTER ""one ""two"" three"" TYPE bigint;
> ALTER TABLE dbversion ALTER COLUMN ""one ""two"" three"" SET STATISTICS -1;
>
> Which still doesn't work.
>
> I don't think that this is a high priority bug, as those who use double quotes in identifiers probably deserve less
thanthe level of quality that pgadmin already provides. 

Yeah, using a column name like "one ""two"" three" really is asking for
trouble and falls firmly in the "Don't do that category".

I've worked up the attached patch for this, but I'm not going to apply
it for the 1.8 release - it'll have to wait for 1.10. Tradionally we've
found that changing the quoting rules tends to subtly break things we
don't think about at the time, which I'm not prepared to do so close to
release.

Thanks for the report.

Regards, Dave
Index: schema/pgFunction.cpp
===================================================================
--- schema/pgFunction.cpp    (revision 6745)
+++ schema/pgFunction.cpp    (working copy)
@@ -409,13 +409,14 @@
                 type = argTypesTkz.GetNextToken();
                 function->iAddArgType(map[type]);

-                // Now add the name, stripping the quotes if
+                // Now add the name, stripping the quotes and \" if
                 // necessary.
                 name = argNamesTkz.GetNextToken();
                 if (!name.IsEmpty())
                 {
                     if (name[0] == '"')
                         name = name.Mid(1, name.Length()-2);
+                    name.Replace(wxT("\\\""), wxT("\""));
                     function->iAddArgName(name);
                 }
                 else
Index: utils/misc.cpp
===================================================================
--- utils/misc.cpp    (revision 6745)
+++ utils/misc.cpp    (working copy)
@@ -392,7 +394,10 @@
     wxString result = value;

     if (needsQuoting(result, true))
+    {
+        result.Replace(wxT("\""), wxT("\"\""));
         return wxT("\"") + result + wxT("\"");
+    }
     else
         return result;
 }
@@ -406,7 +411,10 @@
     wxString result = value;

     if (needsQuoting(result, false))
+    {
+        result.Replace(wxT("\""), wxT("\"\""));
         return wxT("\"") + result + wxT("\"");
+    }
     else
         return result;
 }