Re: Using a different column name in a foreign table

On 1/21/22 10:32 AM, Alanoly Andrews wrote:
> Thanks Adrian. I was aware of that.
>
> But...in the case of FT definition, the quotes are mandatory after column_name in options. And it is a single quote.
> The problem here may be is that the "create foreign table" statement accepts only lower case after options. Remember that the double quotes for the column name in the "select" statement is generated by postgres, not by the end user. If the double quotes were not present, the query with the upper case would have worked.

I have not looked at the source, but I'm betting it is using something like:

https://www.postgresql.org/docs/14/functions-string.html

quote_ident ( text ) → text

Returns the given string suitably quoted to be used as an identifier in
an SQL statement string. Quotes are added only if necessary (i.e., if
the string contains non-identifier characters or would be case-folded).
Embedded quotes are properly doubled. See also Example 43.1.

quote_ident('Foo bar') → "Foo bar"

So:

select quote_ident('MSG_CLNT_HOST_NM');
quote_ident
--------------------
"MSG_CLNT_HOST_NM"

select quote_ident('msg_clnt_host_nm');
quote_ident
------------------
msg_clnt_host_nm

>
> A.A.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com