How to ensure column names are double quoted while using execute format when building a stored procedure?
The following command runs but does not produce results as expected.
Execute Format('insert into stats select %L as id, %2$L as checks,
count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col,
col, t_name, col);
There should be a lot of nulls in columns, but it produces o count.
All columns have got capital letters in. How to ensure that the columns
are double-quote when they are fed in as variables.
Regards,
David
On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
The following command runs but does not produce results as expected.
Execute Format('insert into stats select %L as id, %2$L as checks,
count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col,
col, t_name, col);All columns have got capital letters in. How to ensure that the columns
are double-quote when they are fed in as variables.
Quoting the relevant doc section:
https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT
type (required)
The type of format conversion to use to produce the format specifier's
output. The following types are supported:
s formats the argument value as a simple string. A null value is treated as
an empty string.
I 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
quote_ident).
L quotes the argument value as an SQL literal. A null value is displayed as
the string NULL, without quotes (equivalent to quote_nullable).
David J.
When I used SQL identifier, it stopped working. The command line gets
interpreted as following:
insert into stats select "1" as id, 'count of nulls in
"UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""")
from points_of_interest."pointx_v2_National_Coverage_Sep21" where
"""UNIQUE_REFERENCE_NUMBER""" is null
I used select count("UNIQUE_REFERENCE_NUMBER") from a_table where
"UNIQUE_REFERENCE_NUMBER"
is null in SQL.
It always worked.
This can not be replicated in Execute Format.
Regards,
David
On Thu, 16 Dec 2021 at 20:24, David G. Johnston <david.g.johnston@gmail.com>
wrote:
Show quoted text
On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:The following command runs but does not produce results as expected.
Execute Format('insert into stats select %L as id, %2$L as checks,
count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col,
col, t_name, col);All columns have got capital letters in. How to ensure that the columns
are double-quote when they are fed in as variables.Quoting the relevant doc section:
https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT
type (required)
The type of format conversion to use to produce the format specifier's
output. The following types are supported:s formats the argument value as a simple string. A null value is treated
as an empty string.I 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
quote_ident).L quotes the argument value as an SQL literal. A null value is displayed
as the string NULL, without quotes (equivalent to quote_nullable).David J.
On Thu, Dec 16, 2021 at 2:11 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
When I used SQL identifier, it stopped working. The command line gets
interpreted as following:insert into stats select "1" as id, 'count of nulls in
"UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""")
from points_of_interest."pointx_v2_National_Coverage_Sep21" where
"""UNIQUE_REFERENCE_NUMBER""" is null
By using the correct type specification at each variable insertion you can
get this to work.
Either do trial-and-error or actually reason through what is happening at
each position and why it is either correct or wrong (build up the query in
parts if that makes things easier).
David J.
I did make it to work and have been experimenting on a number of ways. But
it just does not produce expected results.
Regards,
David
On Thu, 16 Dec 2021 at 21:25, David G. Johnston <david.g.johnston@gmail.com>
wrote:
Show quoted text
On Thu, Dec 16, 2021 at 2:11 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:When I used SQL identifier, it stopped working. The command line gets
interpreted as following:insert into stats select "1" as id, 'count of nulls in
"UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""")
from points_of_interest."pointx_v2_National_Coverage_Sep21" where
"""UNIQUE_REFERENCE_NUMBER""" is nullBy using the correct type specification at each variable insertion you can
get this to work.Either do trial-and-error or actually reason through what is happening at
each position and why it is either correct or wrong (build up the query in
parts if that makes things easier).David J.
On Thu, Dec 16, 2021 at 2:33 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:
I did make it to work and have been experimenting on a number of ways.
But it just does not produce expected results.
What exactly did you try, what result did it produce, and what did you
expect it to produce?
David J.
On 12/16/21 13:11, Shaozhong SHI wrote:
When I used SQL identifier, it stopped working. The command line gets
interpreted as following:insert into stats select "1" as id, 'count of nulls in
"UNIQUE_REFERENCE_NUMBER"' as checks,
count("""UNIQUE_REFERENCE_NUMBER""") from
points_of_interest."pointx_v2_National_Coverage_Sep21" where
"""UNIQUE_REFERENCE_NUMBER""" is null
"""UNIQUE_REFERENCE_NUMBER""" is a tip off that your are quoting the
double quotes when passing the identifier name into the format:
select format('select * from %I', '"UNIQUE_REFERENCE_NUMBER"');
format
---------------------------------------------
select * from """UNIQUE_REFERENCE_NUMBER"""
instead of doing as shown here
(https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT),
which is pass in just a string with no double quotes:
select format('select * from %I', 'UNIQUE_REFERENCE_NUMBER');
format
-----------------------------------------
select * from "UNIQUE_REFERENCE_NUMBER"
This:
'count of nulls in "UNIQUE_REFERENCE_NUMBER"'
is just plain incorrect syntax.
I used select count("UNIQUE_REFERENCE_NUMBER") from a_table where
"UNIQUE_REFERENCE_NUMBER" is null in SQL.It always worked.
This can not be replicated in Execute Format.
Regards,
David
On Thu, 16 Dec 2021 at 20:24, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI
<shishaozhong@gmail.com <mailto:shishaozhong@gmail.com>> wrote:The following command runs but does not produce results as expected.
Execute Format('insert into stats select %L as id, %2$L as
checks, count(%3$s) from %4$s where %5$s is null', i, 'count of
nulls in '||col, col, t_name, col);All columns have got capital letters in. How to ensure that the
columns are double-quote when they are fed in as variables.Quoting the relevant doc section:
https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT
<https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT>type (required)
The type of format conversion to use to produce the format
specifier's output. The following types are supported:s formats the argument value as a simple string. A null value is
treated as an empty string.I 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
quote_ident).L quotes the argument value as an SQL literal. A null value is
displayed as the string NULL, without quotes (equivalent to
quote_nullable).David J.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, Dec 16, 2021 at 3:04 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
This:
'count of nulls in "UNIQUE_REFERENCE_NUMBER"'
is just plain incorrect syntax.
<single quote>count of nulls in <double quote>UNIQUE_REFRENCE_NUMBER<double
quote><single quote>
That is not invalid syntax. It is just literal single quoted text - the
double quotes are simply characters in the string.
David J.
On 12/16/21 14:11, David G. Johnston wrote:
On Thu, Dec 16, 2021 at 3:04 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:This:
'count of nulls in "UNIQUE_REFERENCE_NUMBER"'
is just plain incorrect syntax.
<single quote>count of nulls in <double
quote>UNIQUE_REFRENCE_NUMBER<double quote><single quote>That is not invalid syntax. It is just literal single quoted text - the
double quotes are simply characters in the string.
Alright I took it to be what followed:
count("""UNIQUE_REFERENCE_NUMBER""").
Now I see it is a tag for the count.
David J.
--
Adrian Klaver
adrian.klaver@aklaver.com