psql strings and '' - Mailing list pgsql-patches

Currently, psql single-quote argument strings can only embed single
quotes as \', not ''.  This is because while the main psqlscan.l loop
understands '', the subsections used for psql arguments, xslasharg,
doesn't.  This patch attempts to fix that.

However, I am not sure it is done right because I am not using the xe
and xq state values, like the main psql scanner code.  I assume we can
not use them because we are already in xslasharg, and can't add another
state here.

The unusual thing is that in my testing it worked anyway.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Right.  I think the question is whether we want all psql strings to
> > > accept backslashes, and hence not support E'' at all for psql commands.
> > > I figured that made the most sense.
> >
> > I'm not convinced.  Wouldn't it be better if psql commands track the
> > backend syntax?  With standard_conforming_strings on, there will be two
> > ways to tell COPY you want a tab as a delimiter:
> >     DELIMITER '<actual tab char>'
> >     DELIMITER E'\t'
> > and in particular this will NOT do that:
> >     DELIMITER '\t'
>
> Well, I think it a little more confusing that just \copy.  What about \d
> and \set uses of backslashes.  Do they honor standard_conforming_strings
> too?  I assume you are saying they should.
>
> > If we keep '\t' as meaning tab in the \copy syntax then I think we're
> > going to cause confusion in the long run.  I think we should fix \copy
> > and related psql backslash commands to accept E'\t', and make sure that
> > the behavior is the same as the connected backend depending on what its
> > standard_conforming_strings setting is.
>
> OK, though this is going to mean that examples in the psql manual page
> are going to be different for different standard_conforming_strings
> settings:
>
>        testdb=> \set content '\'' `cat my_file.txt` '\''
>        testdb=> INSERT INTO my_table VALUES (:content);
>
> psql doesn't know '''' is about doubling single quotes in a string,
> though \copy does.  The major problem, I think, is that psql often
> follows the shell rules, rather than the SQL rules for most things.
>
> > There is a secondary, largely cosmetic question of whether psql should
> > attempt to prevent you from seeing escape_string_warning messages.
> > I personally have come to the conclusion that escape_string_warning is
> > probably not going to be on by default anyway ;-), and hence it's not
> > worth going to great extremes to prevent this, particularly if it breaks
> > the ability to use psql against pre-8.1 servers.
>
> It does break backward compatibility.
>
> --
>   Bruce Momjian   http://candle.pha.pa.us
>   EnterpriseDB    http://www.enterprisedb.com
>
>   + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.162
diff -c -c -r1.162 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    26 May 2006 19:51:29 -0000    1.162
--- doc/src/sgml/ref/psql-ref.sgml    29 May 2006 17:49:43 -0000
***************
*** 2262,2268 ****
      copy the contents of a file into a table column. First load the file into a
      variable and then proceed as above.
  <programlisting>
! testdb=> <userinput>\set content '\'' `cat my_file.txt` '\''</userinput>
  testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
  </programlisting>
      One possible problem with this approach is that <filename>my_file.txt</filename>
--- 2262,2268 ----
      copy the contents of a file into a table column. First load the file into a
      variable and then proceed as above.
  <programlisting>
! testdb=> <userinput>\set content '''' `cat my_file.txt` ''''</userinput>
  testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
  </programlisting>
      One possible problem with this approach is that <filename>my_file.txt</filename>
***************
*** 2270,2283 ****
      they don't cause a syntax error when the second line is processed. This
      could be done with the program <command>sed</command>:
  <programlisting>
! testdb=> <userinput>\set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\''</userinput>
  </programlisting>
      Observe the correct number of backslashes (6)! It works
      this way: After <application>psql</application> has parsed this
!     line, it passes <literal>sed -e "s/'/\\\'/g" < my_file.txt</literal>
      to the shell. The shell will do its own thing inside the double
      quotes and execute <command>sed</command> with the arguments
!     <literal>-e</literal> and <literal>s/'/\\'/g</literal>. When
      <command>sed</command> parses this it will replace the two
      backslashes with a single one and then do the substitution. Perhaps
      at one point you thought it was great that all Unix commands use the
--- 2270,2283 ----
      they don't cause a syntax error when the second line is processed. This
      could be done with the program <command>sed</command>:
  <programlisting>
! testdb=> <userinput>\set content '''' `sed -e "s/'/\\\\''/g" < my_file.txt` ''''</userinput>
  </programlisting>
      Observe the correct number of backslashes (6)! It works
      this way: After <application>psql</application> has parsed this
!     line, it passes <literal>sed -e "s/'/\\''/g" < my_file.txt</literal>
      to the shell. The shell will do its own thing inside the double
      quotes and execute <command>sed</command> with the arguments
!     <literal>-e</literal> and <literal>s/'/''/g</literal>. When
      <command>sed</command> parses this it will replace the two
      backslashes with a single one and then do the substitution. Perhaps
      at one point you thought it was great that all Unix commands use the
Index: src/bin/psql/psqlscan.l
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/psqlscan.l,v
retrieving revision 1.18
diff -c -c -r1.18 psqlscan.l
*** src/bin/psql/psqlscan.l    11 May 2006 19:15:35 -0000    1.18
--- src/bin/psql/psqlscan.l    29 May 2006 17:49:50 -0000
***************
*** 861,866 ****
--- 861,868 ----

  {quote}            { return LEXRES_OK; }

+ {xqdouble}        { emit("'", 1); }
+
  "\\n"            { appendPQExpBufferChar(output_buf, '\n'); }
  "\\t"            { appendPQExpBufferChar(output_buf, '\t'); }
  "\\b"            { appendPQExpBufferChar(output_buf, '\b'); }