Re: psql strings and '' - Mailing list pgsql-patches

Patch applied.  Thanks.

I see now that a state is not required because we are already in the
single-quote string at that point, comment added.

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


Bruce Momjian wrote:
>
> 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'); }

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

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

  + If your life is a hard drive, Christ can be your backup. +