ERROR: relation "sql_features" does not exist

Pavel Stehule <pavel.stehule@gmail.com> writes:

I tried to write a query that does lateral join between
information_schema.tables and pgstattuple function.

select * from information_schema.tables, lateral(select * from
pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';

The query finished by strange error

postgres=# select * from information_schema.tables, lateral(select * from
pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';
ERROR: relation "sql_features" does not exist

When I set search_path to information_schema, then the query is running.
But there is not any reason why it should be necessary.

Nope, this is classic user error, nothing else. "table_name::name"
is entirely inadequate as a way to reference a table that isn't
visible in your search path. You have to incorporate the schema
name as well.

Ideally you'd just pass the table OID to the OID-accepting version of
pgstattuple(), but of course the information_schema schema views
don't expose OIDs. So basically you need something like

pgstattuple((quote_ident(table_schema)||'.'||quote_ident(table_name))::regclass)

although perhaps format() could help a little here.

regards, tom lane

po 26. 4. 2021 v 19:10 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I tried to write a query that does lateral join between
information_schema.tables and pgstattuple function.

select * from information_schema.tables, lateral(select * from
pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';

The query finished by strange error

postgres=# select * from information_schema.tables, lateral(select * from
pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';
ERROR: relation "sql_features" does not exist

When I set search_path to information_schema, then the query is running.
But there is not any reason why it should be necessary.

Nope, this is classic user error, nothing else. "table_name::name"
is entirely inadequate as a way to reference a table that isn't
visible in your search path. You have to incorporate the schema
name as well.

Ideally you'd just pass the table OID to the OID-accepting version of
pgstattuple(), but of course the information_schema schema views
don't expose OIDs. So basically you need something like

pgstattuple((quote_ident(table_schema)||'.'||quote_ident(table_name))::regclass)

although perhaps format() could help a little here.

I understand now. Thank you for explanation

select * from information_schema.tables, lateral(select * from
pgstattuple(format('%I.%I', table_schema, table_name))) s where table_type
= 'BASE TABLE';

This is working

Regards

Pavel