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+i5Jwa5ZyvWKyrCgfUwO937110VHVTtQwNz52qZMjw-hnLgUQ@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>)
List pgsql-general
Tree view
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 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.

pgsql-general by date:

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