Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 04:28:38PM -0400, Bruce Momjian wrote: On Fri, Mar 20, 2015 at 08:43:21AM -0700, David G. Johnston wrote: On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian br...@momjian.us wrote: It is making a point about nulls and stuff. There are later queries that use format(). I thought maybe you meant those but your specific mention of There are other places later in the docs confused me since you made changes before and after that specific section. Those examples need to be somewhere and it doesn't seem like a undesireable enough setup that major reconstructive surgery is warranted to try and move them elsewhere. Yes, agreed. Patch applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote:
I'm not sure that this particular feature of the standard is something we
should encourage.
Its actually quite useful in this situation, and so maybe the novelty is just
making me nervous, but the only reason I know of this behavior is because
I've
seen a number of posts in just the past couple of years when people
accidentally used this feature and then were surprised when they didn't get an
error. If this stays I would suggest that we take the opportunity to
cross-reference back to where the syntax is defined so people aren't left
scratching their heads as to why it works - or why if they remove the newline
in their own attempt the code suddenly breaks.
Yeah, I am kind on the fence about it, but it is a nice feature,
particulary for PL/pgSQL programs. I added a mention of the string
concatentation feature --- patch attached, and URL updated.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..9fc2a2f
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
--- 1217,1236
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names (strings separated by a
+ newline are concatenated):
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I '
+'WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1306,1320
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 '
!'WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Friday, March 20, 2015, Bruce Momjian br...@momjian.us wrote: On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: I'm not sure that this particular feature of the standard is something we should encourage. Its actually quite useful in this situation, and so maybe the novelty is just making me nervous, but the only reason I know of this behavior is because I've seen a number of posts in just the past couple of years when people accidentally used this feature and then were surprised when they didn't get an error. If this stays I would suggest that we take the opportunity to cross-reference back to where the syntax is defined so people aren't left scratching their heads as to why it works - or why if they remove the newline in their own attempt the code suddenly breaks. Yeah, I am kind on the fence about it, but it is a nice feature, particulary for PL/pgSQL programs. I added a mention of the string concatentation feature --- patch attached, and URL updated. The third option is to just embed a new line in the string itself. Execute Format(’... ...', tbl) USING val David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 1:47 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:
Bruce Momjian wrote:
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
1. The layout of the format version is different, with respect to
newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.
That said, the || there is unnecessary because per standard two literals
'lit1'
'lit2'
are concatenated if they are separated by a newline. So this
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
should suffice.
I'm not sure that this particular feature of the standard is something we
should encourage.
Its actually quite useful in this situation, and so maybe the novelty is
just making me nervous, but the only reason I know of this behavior is
because I've seen a number of posts in just the past couple of years when
people accidentally used this feature and then were surprised when they
didn't get an error. If this stays I would suggest that we take the
opportunity to cross-reference back to where the syntax is defined so
people aren't left scratching their heads as to why it works - or why if
they remove the newline in their own attempt the code suddenly breaks.
David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 06:53:29PM -0700, David G. Johnston wrote: On Friday, March 20, 2015, Bruce Momjian br...@momjian.us wrote: On Fri, Mar 20, 2015 at 05:50:03PM -0700, David G. Johnston wrote: I'm not sure that this particular feature of the standard is something we should encourage. Its actually quite useful in this situation, and so maybe the novelty is just making me nervous, but the only reason I know of this behavior is because I've seen a number of posts in just the past couple of years when people accidentally used this feature and then were surprised when they didn't get an error. If this stays I would suggest that we take the opportunity to cross-reference back to where the syntax is defined so people aren't left scratching their heads as to why it works - or why if they remove the newline in their own attempt the code suddenly breaks. Yeah, I am kind on the fence about it, but it is a nice feature, particulary for PL/pgSQL programs. I added a mention of the string concatentation feature --- patch attached, and URL updated. The third option is to just embed a new line in the string itself. Execute Format(’... ...', tbl) USING val True, but that just looks odd. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Mar 19, 2015 at 06:05:52PM -0700, David G. Johnston wrote: On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian br...@momjian.us wrote: There are other places later in the docs where we explain all the quote* functions and show examples of query construction using string concatenation, but I am not sure how we can remove those. Can you be more specific? Yes. You can see the output of the attached patch here: http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Notice: EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue); and EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue); It is making a point about nulls and stuff. There are later queries that use format(). I thought maybe you meant those but your specific mention of There are other places later in the docs confused me since you made changes before and after that specific section. Those examples need to be somewhere and it doesn't seem like a undesireable enough setup that major reconstructive surgery is warranted to try and move them elsewhere. On a related note: If you are dealing with values that might be null, you should usually use quote_nullable in place of quote_literal. Its unclear why, aside from semantic uncleanliness, someone would use quote_literal given its identical behavior for non-null values and inferior behavior which passed NULL. The function table for the two could maybe be more clear since quote_nullable(NULL) returns a string representation of NULL without any quotes while quote_literal(NULL) returns an actual NULL that ultimately poisons the string concatenation that these functions are used with. reads some more The differences between the actual null and the string NULL are strictly in capitalization - which is not consistent even within the table. concat_ws states NULL arguments are ignored and so represents actual null with all-caps which is string NULL in the quote_* descriptions. Having read 40.5.4 and example 40-1 the difference is clear and obvious so maybe what is in the table is sufficient for this topic. I would suggest adding a comment to quote_ident and quote_nullable that corresponding format codes are %I and %L. Obviously there is no quote_ function to correspond with %S. There is likewise nor corresponding format code for quote_literal since quote_nullable is superior in every way (that I can tell at least). OK, I have added that tip --- good suggestion. Patch attached. I was actually referring to chapter 9 http://www.postgresql.org/docs/9.4/interactive/functions-string.html The table definitions of the quote_* function should have a comment about their equivalency to format %I and %L Also, in 9.4.1 (format - type) would be the most obvious place for the equivalency of the format %I and %L to quote_* IMO too much is trying to be done within example 40-1 (for instance, the quote_literal/nullable explanation should be moved elsewhere); and while these are mainly useful with dynamic SQL it still behooves us to put the definition stuff in the structural area and then use the example for comprehension and clarification regarding best practices (i.e., format for %I but USING for literals - though I know some would say we should necessarily express those kinds of opinions in the docs...). That said, it is not as bad as I may seem to be making it out to be and aside from wanting to put and obvious reference to format directly next to the quote_* functions is more style that content. The desire for the linkage is strong though because we want someone who naturally would use string concatenation and the quote_* functions to be made aware of, and convinced to use (they will thank us for this), the format() function instead. David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
Looking at
http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
The paired example at the top of the patch has two things worth considering.
1. The layout of the format version is different, with respect to newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
2. There is a recent posting pointing out the fact that the first query did
not use quote_ident(tabname) but instead did tabname::regclass, which calls
quote_ident internally. While there is a choice is that situation with
format you must pass in an unquoted label and so must not use
tabname::regclass. I think the first example should be written to use
quote_ident(tabname).
As regards the ::regclass behavior I would need see it current treatment
and recommended usage in the docs in order to form an opinion on how it
interacts with quote_literal and %I.
David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote:
Bruce Momjian wrote:
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
1. The layout of the format version is different, with respect to
newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.
That said, the || there is unnecessary because per standard two literals
'lit1'
'lit2'
are concatenated if they are separated by a newline. So this
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
should suffice.
OK, I used your idea, patch attached.
BTW very long lines are undesirable because they are truncated in the
PDF output.
True, but the length was only 95 characters --- is that too long for our
PDFs?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..bb09479
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
--- 1217,1235
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I '
+'WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1305,1319
/para
para
! Dynamic values require careful handling since
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
Bruce Momjian wrote: On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: BTW very long lines are undesirable because they are truncated in the PDF output. True, but the length was only 95 characters --- is that too long for our PDFs? I built some PDFs when I did the ALTER USER CURRENT_USER patch, and it seemed to me that synposes ought to limit to about 85 chars. Maybe some more still fit in the page, but I think 95 would be a bit too much. I'm not sure how the code you're writing here is indented, though; you'd have to build it and give it a look. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 06:05:35PM -0300, Alvaro Herrera wrote: Bruce Momjian wrote: On Fri, Mar 20, 2015 at 05:47:49PM -0300, Alvaro Herrera wrote: BTW very long lines are undesirable because they are truncated in the PDF output. True, but the length was only 95 characters --- is that too long for our PDFs? I built some PDFs when I did the ALTER USER CURRENT_USER patch, and it seemed to me that synposes ought to limit to about 85 chars. Maybe some more still fit in the page, but I think 95 would be a bit too much. I'm not sure how the code you're writing here is indented, though; you'd have to build it and give it a look. OK, thanks, good to know. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 08:43:21AM -0700, David G. Johnston wrote:
On Thu, Mar 19, 2015 at 6:49 PM, Bruce Momjian br...@momjian.us wrote:
It is making a point about nulls and stuff. There are later queries
that use format().
I thought maybe you meant those but your specific mention of
There are other places later in the docs confused me since you made changes
before and after that specific section.
Those examples need to be somewhere and it doesn't seem like a undesireable
enough setup that major reconstructive surgery is warranted to try and move
them elsewhere.
Yes, agreed.
I was actually referring to chapter 9
http://www.postgresql.org/docs/9.4/interactive/functions-string.html
The table definitions of the quote_* function should have a comment about
their equivalency to format %I and %L
I think it is going to be awkward to mention a much more complex
function, format(), when covering a simle quote function.
Also, in 9.4.1 (format - type) would be the most obvious place for the
equivalency of the format %I and %L to quote_*
Yes, added.
Update patch attached and URL udpated with current patch too.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..aee8264
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM '
*** 1222,1227
--- 1222,1234
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
*** EXECUTE 'UPDATE tbl SET '
*** 1393,1407
programlisting
EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
/programlisting
The functionformat/function function can be used in conjunction with
the literalUSING/literal clause:
programlisting
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
/programlisting
! This form is more
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
Looking at http://momjian.us/tmp/pgsql/plpgsql-statements.html#
PLPGSQL-STATEMENTS-EXECUTING-DYN
The paired example at the top of the patch has two things worth considering.
1. The layout of the format version is different, with respect to newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
2. There is a recent posting pointing out the fact that the first query did
not
use quote_ident(tabname) but instead did tabname::regclass, which calls
quote_ident internally. While there is a choice is that situation with format
you must pass in an unquoted label and so must not use tabname::regclass. I
think the first example should be written to use quote_ident(tabname).
Ah, good point. Updated patch attached, and URL updated.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index aa19e10..3195655
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 2998,3011
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null.
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes.
/para
/listitem
/itemizedlist
--- 2998,3012
para
literalI/literal treats the argument value as an SQL
identifier, double-quoting it if necessary.
! It is an error for the value to be null (equivalent to
! functionquote_ident/).
/para
/listitem
listitem
para
literalL/literal quotes the argument value as an SQL literal.
A null value is displayed as the string literalNULL/, without
! quotes (equivalent to functionquote_nullable/function).
/para
/listitem
/itemizedlist
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..451cbb4
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM mytable WH
*** 1217,1227
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
--- 1217,1234
dynamically selected table, you could do this:
programlisting
EXECUTE 'SELECT count(*) FROM '
! || quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted lt;= $2'
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
!
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
Bruce Momjian wrote:
On Fri, Mar 20, 2015 at 08:54:24AM -0700, David G. Johnston wrote:
1. The layout of the format version is different, with respect to newlines,
than the quote version; but while using newlines for the mandatory
concatenation is good having an excessively long format string isn't
desirable
and so maybe we should show something like:
EXECUTE format('SELECT count(*) FROM %I '
|| 'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
I think that is very confusing --- the idea is that we don't need to use
|| with format, but you are then using || to span multiple lines.
That || seems fine, since it's only used for a line continuation; having
|| scattered all over the query string to interpolate each variable is
much more unreadable.
That said, the || there is unnecessary because per standard two literals 'lit1'
'lit2'
are concatenated if they are separated by a newline. So this
EXECUTE format('SELECT count(*) FROM %I '
'WHERE inserted_by = $1 AND insert = $2', tabname)
INTO c
USING checked_user, checked_date
should suffice.
BTW very long lines are undesirable because they are truncated in the
PDF output.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Mar 19, 2015 at 04:01:32PM -0700, David G. Johnston wrote: Prefacing it with: You may also see the following syntax in the wild since format was only recently introduced. may solve your lack of reason for inclusion. Uh, the problem with that is we are not going to revisit this when format isn't recently introduced. I think script writers naturally think of query construction using string concatenation first, so showing it first seems fine. +1 There are other places later in the docs where we explain all the quote* functions and show examples of query construction using string concatenation, but I am not sure how we can remove those. Can you be more specific? On a related note: If you are dealing with values that might be null, you should usually use quote_nullable in place of quote_literal. Its unclear why, aside from semantic uncleanliness, someone would use quote_literal given its identical behavior for non-null values and inferior behavior which passed NULL. The function table for the two could maybe be more clear since quote_nullable(NULL) returns a string representation of NULL without any quotes while quote_literal(NULL) returns an actual NULL that ultimately poisons the string concatenation that these functions are used with. reads some more The differences between the actual null and the string NULL are strictly in capitalization - which is not consistent even within the table. concat_ws states NULL arguments are ignored and so represents actual null with all-caps which is string NULL in the quote_* descriptions. Having read 40.5.4 and example 40-1 the difference is clear and obvious so maybe what is in the table is sufficient for this topic. I would suggest adding a comment to quote_ident and quote_nullable that corresponding format codes are %I and %L. Obviously there is no quote_ function to correspond with %S. There is likewise nor corresponding format code for quote_literal since quote_nullable is superior in every way (that I can tell at least). David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Thu, Oct 2, 2014 at 09:06:54PM -0700, David G Johnston wrote:
Jim Nasby-5 wrote
On 10/2/14, 6:51 AM, Pavel Stehule wrote:
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
-1, because of quoting issues
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
Better, but I think it should really be quote_ident( colname )
http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
The use of %I and %L solve all quoting issues when using format(); they
likely call the relevant quote_ function on the user's behalf.
Doing some research on EXECUTE, I found that for constants, USING is
best because it _conditionally_ quotes based on the data type, and for
identifiers, format(%I) is best.
A old examples are very instructive, but little bit less readable and
maybe too complex for beginners.
Opinions?
Honestly, I'm not to fond of either. format() is a heck of a lot nicer
than a forest of ||'s, but I think it still falls short of what we'd
really want here which is some kind of variable substitution or even a
templating language. IE:
EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
Putting that example into the docs isn't a good idea...it isn't valid in
PostgreSQL ;)
My complaint with the topic is that it is not specific enough. There are
quite a few locations with dynamic queries. My take is that the
concatenation form be shown only in possible ways to accomplish this type
sections but that all actual examples or recommendations make use of the
format function.
I have done this with the attached PL/pgSQL doc patch.
The link above (40.5.4 in 9.4) is one such section where both forms need to
be showed but I would suggest reversing the order so that we first introduce
- prominently - the format function and then show the old-school way. That
said there is some merit to emphasizing the wrong and hard way so as to help
the reader conclude that the less painful format function really is their
best friend...but that would be my fallback position here.
I tried showing format() first, but then it was odd about why to then
show ||. I ended up showing || first, then showing format() and saying
it is better.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..52b4daa
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM '
*** 1222,1227
--- 1222,1234
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
*** EXECUTE format('UPDATE tbl SET %I = %L W
*** 1399,1407
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
/programlisting
! This form is more efficient, because the parameters
! literalnewvalue/literal and literalkeyvalue/literal are not
! converted to text.
/para
/example
--- 1409,1417
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
/programlisting
! This form is better because the variables are emphasisoptionally/
! quoted based on their
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Thu, Mar 19, 2015 at 3:38 PM, Bruce Momjian br...@momjian.us wrote:
On Thu, Oct 2, 2014 at 09:06:54PM -0700, David G Johnston wrote:
Jim Nasby-5 wrote
On 10/2/14, 6:51 AM, Pavel Stehule wrote:
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
-1, because of quoting issues
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
Better, but I think it should really be quote_ident( colname )
http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
The use of %I and %L solve all quoting issues when using format(); they
likely call the relevant quote_ function on the user's behalf.
Doing some research on EXECUTE, I found that for constants, USING is
best because it _conditionally_ quotes based on the data type, and for
identifiers, format(%I) is best.
On a nit-pick note, ISTM that EXECUTE 'SELECT $1' USING ('1')
is not really optionally quoted based on their data types but rather
processed in such a way as to not require quoting at all. Doesn't execute
effectively bypass converting the USING values to text in much the same way
as PREPARE/EXECUTE does in SQL? i.e., It uses the extended query protocol
with a separate BIND instead of interpolating the arguments and then using
a simple query protocol.
Not that the reader likely cares - they just need to know to never place
%I, %L or $# within quotes. I would say the same goes for %S always
unless forced to do otherwise.
A old examples are very instructive, but little bit less readable and
maybe too complex for beginners.
Opinions?
Honestly, I'm not to fond of either. format() is a heck of a lot nicer
than a forest of ||'s, but I think it still falls short of what we'd
really want here which is some kind of variable substitution or even a
templating language. IE:
EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
Putting that example into the docs isn't a good idea...it isn't valid in
PostgreSQL ;)
My complaint with the topic is that it is not specific enough. There are
quite a few locations with dynamic queries. My take is that the
concatenation form be shown only in possible ways to accomplish this
type
sections but that all actual examples or recommendations make use of the
format function.
I have done this with the attached PL/pgSQL doc patch.
Thank You!
The link above (40.5.4 in 9.4) is one such section where both forms need
to
be showed but I would suggest reversing the order so that we first
introduce
- prominently - the format function and then show the old-school way.
That
said there is some merit to emphasizing the wrong and hard way so as to
help
the reader conclude that the less painful format function really is their
best friend...but that would be my fallback position here.
I tried showing format() first, but then it was odd about why to then
show ||. I ended up showing || first, then showing format() and saying
it is better.
Prefacing it with: You may also see the following syntax in the wild
since format was only recently introduced.
may solve your lack of reason for inclusion.
Neither item requires attention but some food for thought.
David J.
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Thu, Mar 19, 2015 at 04:01:32PM -0700, David G. Johnston wrote:
Doing some research on EXECUTE, I found that for constants, USING is
best because it _conditionally_ quotes based on the data type, and for
identifiers, format(%I) is best.
On a nit-pick note, ISTM that EXECUTE 'SELECT $1' USING ('1')
is not really optionally quoted based on their data types but rather
processed in such a way as to not require quoting at all. Doesn't execute
effectively bypass converting the USING values to text in much the same way as
PREPARE/EXECUTE does in SQL? i.e., It uses the extended query protocol with a
separate BIND instead of interpolating the arguments and then using a simple
query protocol.
Not that the reader likely cares - they just need to know to never place %I,
%L or $# within quotes. I would say the same goes for %S always unless
forced
to do otherwise.
You are correct. I have modified that paragraph in the attached
version. Not only is %L inefficient, but converting to text can cause
errors, e.g. adding two strings throws an error:
test= do $$ declare x text; begin execute format('select %L + ''2''',
1) into x; raise '%', x; end;$$;
ERROR: operator is not unique: unknown + unknown
LINE 1: select '1' + '2'
^
HINT: Could not choose a best candidate operator. You might need to
add explicit type casts.
QUERY: select '1' + '2'
CONTEXT: PL/pgSQL function inline_code_block line 1 at EXECUTE
statement
while adding an integer to a string works:
test= do $$ declare x text; begin execute format('select $1 + ''2''')
using 1 into x; raise '%', x; end;$$;
ERROR: 3
The link above (40.5.4 in 9.4) is one such section where both forms need
to
be showed but I would suggest reversing the order so that we first
introduce
- prominently - the format function and then show the old-school way.
That
said there is some merit to emphasizing the wrong and hard way so as to
help
the reader conclude that the less painful format function really is
their
best friend...but that would be my fallback position here.
I tried showing format() first, but then it was odd about why to then
show ||. I ended up showing || first, then showing format() and saying
it is better.
Prefacing it with: You may also see the following syntax in the wild since
format was only recently introduced.
may solve your lack of reason for inclusion.
Uh, the problem with that is we are not going to revisit this when
format isn't recently introduced. I think script writers naturally
think of query construction using string concatenation first, so showing
it first seems fine.
There are other places later in the docs where we explain all the quote*
functions and show examples of query construction using string
concatenation, but I am not sure how we can remove those.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..eb80169
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM '
*** 1222,1227
--- 1222,1234
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On Thu, Mar 19, 2015 at 06:05:52PM -0700, David G. Johnston wrote:
On Thu, Mar 19, 2015 at 5:18 PM, Bruce Momjian br...@momjian.us wrote:
There are other places later in the docs where we explain all the quote*
functions and show examples of query construction using string
concatenation, but I am not sure how we can remove those.
Can you be more specific?
Yes. You can see the output of the attached patch here:
http://momjian.us/tmp/pgsql/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Notice:
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_nullable(newvalue)
|| ' WHERE key = '
|| quote_nullable(keyvalue);
and
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE key = '
|| quote_literal(keyvalue);
It is making a point about nulls and stuff. There are later queries
that use format().
On a related note:
If you are dealing with values that might be null, you should usually use
quote_nullable in place of quote_literal.
Its unclear why, aside from semantic uncleanliness, someone would use
quote_literal given its identical behavior for non-null values and inferior
behavior which passed NULL. The function table for the two could maybe be
more
clear since quote_nullable(NULL) returns a string representation of NULL
without any quotes while quote_literal(NULL) returns an actual NULL that
ultimately poisons the string concatenation that these functions are used
with.
reads some more
The differences between the actual null and the string NULL are strictly in
capitalization - which is not consistent even within the table. concat_ws
states NULL arguments are ignored and so represents actual null with
all-caps
which is string NULL in the quote_* descriptions. Having read 40.5.4 and
example 40-1 the difference is clear and obvious so maybe what is in the table
is sufficient for this topic.
I would suggest adding a comment to quote_ident and quote_nullable that
corresponding format codes are %I and %L. Obviously there is no quote_
function to correspond with %S. There is likewise nor corresponding format
code for quote_literal since quote_nullable is superior in every way (that I
can tell at least).
OK, I have added that tip --- good suggestion. Patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 158d9d2..aee8264
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** EXECUTE 'SELECT count(*) FROM '
*** 1222,1227
--- 1222,1234
INTO c
USING checked_user, checked_date;
/programlisting
+ A cleaner approach is to use functionformat()/'s literal%I/
+ specification for table or column names:
+ programlisting
+ EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1 AND inserted lt;= $2', tabname)
+INTO c
+USING checked_user, checked_date;
+ /programlisting
Another restriction on parameter symbols is that they only work in
commandSELECT/, commandINSERT/, commandUPDATE/, and
commandDELETE/ commands. In other statement
*** EXECUTE 'SELECT count(*) FROM '
*** 1297,1307
/para
para
! Dynamic values that are to be inserted into the constructed
! query require careful handling since they might themselves contain
quote characters.
! An example (this assumes that you are using dollar quoting for the
! function as a whole, so the quote marks need not be doubled):
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
--- 1304,1317
/para
para
! Dynamic values require careful handling since they might contain
quote characters.
! An example using functionformat()/ (this assumes that you are
! dollar quoting the function body so quote marks need not be doubled):
! programlisting
! EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
! /programlisting
! It is also possible to call the quoting functions directly:
programlisting
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
*** EXECUTE 'UPDATE tbl SET '
*** 1393,1407
programlisting
EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);
/programlisting
The functionformat/function function can be used in conjunction with
the literalUSING/literal clause:
programlisting
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
USING newvalue, keyvalue;
/programlisting
! This form is more
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On 10/2/14, 11:06 PM, David G Johnston wrote:
Jim Nasby-5 wrote
On 10/2/14, 6:51 AM, Pavel Stehule wrote:
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
-1, because of quoting issues
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
Better, but I think it should really be quote_ident( colname )
http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
The use of %I and %L solve all quoting issues when using format(); they
likely call the relevant quote_ function on the user's behalf.
Right. Duh.
A old examples are very instructive, but little bit less readable and
maybe too complex for beginners.
Opinions?
Honestly, I'm not to fond of either. format() is a heck of a lot nicer
than a forest of ||'s, but I think it still falls short of what we'd
really want here which is some kind of variable substitution or even a
templating language. IE:
EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
Putting that example into the docs isn't a good idea...it isn't valid in
PostgreSQL ;)
My point was that format() still isn't what we really need for dynamic SQL, and
we should come up with something better.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal: doc: simplify examples of dynamic SQL
Hi
There are few less readable examples of dynamic SQL in plpgsql doc
like:
EXECUTE 'SELECT count(*) FROM '
|| tabname::regclass
|| ' WHERE inserted_by = $1 AND inserted = $2'
INTO c
USING checked_user, checked_date;
or
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $'
|| newvalue
|| '$ WHERE key = '
|| quote_literal(keyvalue);
We can show a examples based on format function only:
EXECUTE format('SELECT count(*) FROM %I'
' WHERE inserted_by = $1 AND inserted = $2',
tabname)
INTO c
USING checked_user, checked_date;
or
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
A old examples are very instructive, but little bit less readable and maybe
too complex for beginners.
Opinions?
Regards
Pavel
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
On 10/2/14, 6:51 AM, Pavel Stehule wrote:
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
-1, because of quoting issues
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
Better, but I think it should really be quote_ident( colname )
A old examples are very instructive, but little bit less readable and maybe too
complex for beginners.
Opinions?
Honestly, I'm not to fond of either. format() is a heck of a lot nicer than a
forest of ||'s, but I think it still falls short of what we'd really want here
which is some kind of variable substitution or even a templating language. IE:
EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: doc: simplify examples of dynamic SQL
Jim Nasby-5 wrote
On 10/2/14, 6:51 AM, Pavel Stehule wrote:
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = %L',
colname, keyvalue)
or
-1, because of quoting issues
EXECUTE format('UPDATE tbl SET %I = newvalue WHERE key = $1',
colname)
USING keyvalue;
Better, but I think it should really be quote_ident( colname )
http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
The use of %I and %L solve all quoting issues when using format(); they
likely call the relevant quote_ function on the user's behalf.
A old examples are very instructive, but little bit less readable and
maybe too complex for beginners.
Opinions?
Honestly, I'm not to fond of either. format() is a heck of a lot nicer
than a forest of ||'s, but I think it still falls short of what we'd
really want here which is some kind of variable substitution or even a
templating language. IE:
EXECUTE 'UDPATE tbl SET $colname = newvalue WHERE key = $keyvalue';
Putting that example into the docs isn't a good idea...it isn't valid in
PostgreSQL ;)
My complaint with the topic is that it is not specific enough. There are
quite a few locations with dynamic queries. My take is that the
concatenation form be shown only in possible ways to accomplish this type
sections but that all actual examples or recommendations make use of the
format function.
The link above (40.5.4 in 9.4) is one such section where both forms need to
be showed but I would suggest reversing the order so that we first introduce
- prominently - the format function and then show the old-school way. That
said there is some merit to emphasizing the wrong and hard way so as to help
the reader conclude that the less painful format function really is their
best friend...but that would be my fallback position here.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/proposal-doc-simplify-examples-of-dynamic-SQL-tp5821379p5821532.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
