Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

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