hello
here is patch
pa...@postgres:5432=# \set foo 'hello world'
pa...@postgres:5432=# SELECT :'foo' AS :"foo";
hello world
-------------
hello world
(1 row)
Regards
Pavel
2009/12/29 Pavel Stehule <pavel.steh...@gmail.com>:
> 2009/12/29 Tom Lane <t...@sss.pgh.pa.us>:
>> Pavel Stehule <pavel.steh...@gmail.com> writes:
>>> 2009/12/29 Alvaro Herrera <alvhe...@commandprompt.com>:
>>>> Can we use a trick similar to pg_dump's?
>>
>>> I see it - we can move function (content) fmtId from dumputils.c to libpq.
>>
>> This is not a good idea. pg_dump can be expected to be up-to-date with
>> the backend's keyword list, but libpq cannot.
>>
>> Just quote the thing unconditionally. It's not worth working harder
>> than that anyway.
>
> I see it.
>
> Pavel
>
>>
>> regards, tom lane
>>
>
*** ./doc/src/sgml/ref/psql-ref.sgml.orig 2009-12-25 00:36:39.000000000 +0100
--- ./doc/src/sgml/ref/psql-ref.sgml 2009-12-29 20:07:05.070849742 +0100
***************
*** 2335,2340 ****
--- 2335,2361 ----
</note>
<para>
+ <application>psql</application> provides two additional syntax for
+ retrieving the content of variable. This auxilary syntax ensure
+ necessary escaping when we would to use content as sql literal or
+ sql identifier.
+ <programlisting>
+ testdb=> <userinput>\set foo 'hello world'</userinput>
+ testdb=> <userinput>\echo :'foo'</userinput>
+ 'hello world'
+
+ testdb=> <userinput>\echo :"foo"</userinput>
+ "hello world"
+
+ testdb=> <userinput>SELECT :'foo' AS :"foo";</userinput>
+ hello world
+ -------------
+ hello world
+ (1 row)
+ </programlisting>
+ </para>
+
+ <para>
If you call <command>\set</command> without a second argument, the
variable is set, with an empty string as value. To unset (or delete) a
variable, use the command <command>\unset</command>.
***************
*** 2722,2728 ****
the variable is copied literally, so it can even contain unbalanced
quotes or backslash commands. You must make sure that it makes sense
where you put it. Variable interpolation will not be performed into
! quoted <acronym>SQL</acronym> entities.
</para>
<para>
--- 2743,2755 ----
the variable is copied literally, so it can even contain unbalanced
quotes or backslash commands. You must make sure that it makes sense
where you put it. Variable interpolation will not be performed into
! quoted <acronym>SQL</acronym> entities. Identifiers with special chars
! have to be inserted between double quotes. <application>psql</application>
! ensure necessary quoting with additional syntax:
! <programlisting>
! testdb=> <userinput>\set foo 'my tab'</userinput>
! testdb=> <userinput>SELECT * FROM :"foo";</userinput>
! </programlisting>
</para>
<para>
***************
*** 2752,2757 ****
--- 2779,2793 ----
at one point you thought it was great that all Unix commands use the
same escape character.)
</para>
+
+ <para>
+ With alternative syntax for retrieving content of variables external
+ escaping are not necessary:
+ <programlisting>
+ testdb=> <userinput>\set content `cat my_file.txt`</userinput>
+ testdb=> <userinput>INSERT INTO my_table VALUES(:'content');</userinput>
+ </programlisting>
+ </para>
<para>
Since colons can legally appear in SQL commands, the following rule
*** ./src/bin/psql/psqlscan.l.orig 2009-11-12 01:13:00.000000000 +0100
--- ./src/bin/psql/psqlscan.l 2009-12-29 21:17:06.876851879 +0100
***************
*** 119,124 ****
--- 119,127 ----
static void emit(const char *txt, int len);
static bool is_utf16_surrogate_first(uint32 c);
+ static char *quote_literal(const char *value);
+ static char *quote_ident(const char *rawid);
+
#define ECHO emit(yytext, yyleng)
%}
***************
*** 707,712 ****
--- 710,772 ----
}
}
+ :'[A-Za-z0-9_]+' {
+ /*
+ * Possible psql variable substitution
+ * with literal quoting.
+ */
+ const char *value;
+
+ yytext[yyleng - 1] = '\0';
+ value = GetVariable(pset.vars, yytext + 2);
+
+ if (value)
+ {
+ /* It is a variable, perform substitution */
+ char *qvalue = quote_literal(value);
+
+ push_new_buffer(qvalue);
+ free(qvalue);
+ /* yy_scan_string already made buffer active */
+ }
+ else
+ {
+ /*
+ * if the variable doesn't exist we'll copy the
+ * string as is
+ */
+ ECHO;
+ }
+ }
+
+ :\"[A-Za-z0-9_]+\" {
+ /* Possible psql variable substitution */
+ const char *value;
+
+ /* remove dquotes */
+ yytext[yyleng - 1] = '\0';
+ value = GetVariable(pset.vars, yytext + 2);
+
+ if (value)
+ {
+ /* It is a variable, perform substitution */
+ char *qvalue = quote_ident(value);
+
+ push_new_buffer(qvalue);
+ free(qvalue);
+ /* yy_scan_string already made buffer active */
+ }
+ else
+ {
+ /*
+ * if the variable doesn't exist we'll copy the
+ * string as is
+ */
+ ECHO;
+ }
+ }
+
+
/*
* Back to backend-compatible rules.
*/
***************
*** 927,932 ****
--- 987,1044 ----
return LEXRES_OK;
}
+ :'[A-Za-z0-9_]*' {
+ /* Possible psql variable substitution */
+ if (option_type == OT_VERBATIM)
+ ECHO;
+ else
+ {
+ const char *value;
+
+ yytext[yyleng - 1] = '\0';
+ value = GetVariable(pset.vars, yytext + 2);
+
+ if (value)
+ {
+ char *qvalue = quote_literal(value);
+
+ appendPQExpBufferStr(output_buf, qvalue);
+ free(qvalue);
+ }
+ }
+
+ *option_quote = ':';
+
+ return LEXRES_OK;
+ }
+
+ :\"[A-Za-z0-9_]*\" {
+ /* Possible psql variable substitution */
+ if (option_type == OT_VERBATIM)
+ ECHO;
+ else
+ {
+ const char *value;
+
+ yytext[yyleng - 1] = '\0';
+ value = GetVariable(pset.vars, yytext + 2);
+
+ if (value)
+ {
+ char *qvalue = quote_ident(value);
+
+
+ appendPQExpBufferStr(output_buf, qvalue);
+ free(qvalue);
+ }
+ }
+
+ *option_quote = ':';
+
+ return LEXRES_OK;
+ }
+
+
"|" {
ECHO;
if (option_type == OT_FILEPIPE)
***************
*** 1740,1742 ****
--- 1852,1913 ----
{
return (c >= 0xD800 && c <= 0xDBFF);
}
+
+ /*
+ * escape literal
+ *
+ */
+ static char *
+ quote_literal(const char *value)
+ {
+ char *result;
+ size_t len = strlen(value);
+ size_t rlen;
+ int error;
+
+ result = pg_malloc(len * 2 + 3);
+
+ rlen = PQescapeStringConn(pset.db, result+1, value, len, &error);
+
+ if (error != 0)
+ {
+ /* can't to escape value */
+ fprintf(stderr, "invalid PQescapeStringConn result\n");
+ exit(1);
+ }
+
+ result[0] = '\'';
+ result[++rlen] = '\'';
+ result[++rlen] = '\0';
+
+ return result;
+ }
+
+ /*
+ * escape identifier
+ *
+ */
+ static char *
+ quote_ident(const char *rawid)
+ {
+ PQExpBufferData pqbuf;
+ const char *cp;
+
+ initPQExpBuffer(&pqbuf);
+
+ appendPQExpBufferChar(&pqbuf, '\"');
+ for (cp = rawid; *cp; cp++)
+ {
+ /*
+ * Did we find a double-quote in the string? Then make this a
+ * double double-quote per SQL99. Before, we put in a
+ * backslash/double-quote pair. - thomas 2000-08-05
+ */
+ if (*cp == '\"')
+ appendPQExpBufferChar(&pqbuf, '\"');
+ appendPQExpBufferChar(&pqbuf, *cp);
+ }
+ appendPQExpBufferChar(&pqbuf, '\"');
+
+ return pqbuf.data;
+ }
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
