Re: Get table catalog from pg_indexes

On 11/27/22 10:42, Igor Korot wrote:
> Hi, Erik,
>
> On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold <ewie(at)ewie(dot)name> wrote:
>>
>>> On 27/11/2022 18:22 CET Igor Korot <ikorot01(at)gmail(dot)com> wrote:
>>>
>>> Table pg_indexes does not contain a field for a catalog.
>>>
>>> So how do I get that?
>>>
>>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
>>> schemaname = $3
>>
>> Use SELECT current_database() if you need to know the catalog.
>> pg_indexes only covers the current database[1].
>
>>From the lin yo referenced:
>
> [quote]
> The view pg_indexes provides access to useful information about each
> index in the database.
> [/quote]
>
> It doesn't say anything about "current" DB - only the DB.

https://www.postgresql.org/docs/current/catalogs-overview.html

"Most system catalogs are copied from the template database during
database creation and are thereafter database-specific. A few catalogs
are physically shared across all databases in a cluster; these are noted
in the descriptions of the individual catalogs."

As in:

https://www.postgresql.org/docs/current/catalog-pg-database.html

"Unlike most system catalogs, pg_database is shared across all databases
of a cluster: there is only one copy of pg_database per cluster, not one
per database."

>
> However, I think I can try "SELECT 1 FROM <catalog>.pg_indexes...".
> Will this work?
>
> Thank you.
>
>>
>> [1] https://www.postgresql.org/docs/current/view-pg-indexes.html
>>
>> --
>> Erik
>
>

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