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@ewie.name> wrote:
>>
>>> On 27/11/2022 18:22 CET Igor Korot <ikorot01@gmail.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.klaver@aklaver.com