Postgres Pro
Downloads
Home   >   mailing lists

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

From Shaozhong SHI
Subject Re: How to ensure column names are double quoted while using execute format when building a stored procedure?
Date
Msg-id CA+i5JwbDUx6kWe5bkWJkHJjEVOEuojWJSxLfnxDQ5Cj_+v7XnQ@mail.gmail.com
Whole thread Raw
In response to Re: How to ensure column names are double quoted while using execute format when building a stored procedure?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: How to ensure column names are double quoted while using execute format when building a stored procedure?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: How to ensure column names are double quoted while using execute format when building a stored procedure?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Tree view
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:

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.

pgsql-general by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
Next
From: "David G. Johnston"
Date:
Subject: Re: How to ensure column names are double quoted while using execute format when building a stored procedure?