Re: psql strings and ''

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(at)candle(dot)pha(dot)pa(dot)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=&gt; <userinput>\set content '\'' `cat my_file.txt` '\''</userinput>
> testdb=&gt; <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=&gt; <userinput>\set content '''' `cat my_file.txt` ''''</userinput>
> testdb=&gt; <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=&gt; <userinput>\set content '\'' `sed -e "s/'/\\\\\\'/g" &lt; 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" &lt; 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=&gt; <userinput>\set content '''' `sed -e "s/'/\\\\''/g" &lt; 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" &lt; 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. +