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

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(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI <shishaozhong(at)gmail(dot)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.
>
>