Thread: How to ensure column names are double quoted while using execute format when building a stored procedure?
How to ensure column names are double quoted while using execute format when building a stored procedure?
From
Shaozhong SHI
Date:
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
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
"David G. Johnston"
Date:
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:
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.
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
Shaozhong SHI
Date:
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:
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: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.
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
"David G. Johnston"
Date:
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.
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
Shaozhong SHI
Date:
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:
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.
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
"David G. Johnston"
Date:
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.
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
Adrian Klaver
Date:
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
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
"David G. Johnston"
Date:
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.
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
From
Adrian Klaver
Date:
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