From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: ERROR: relation "sql_features" does not exist |
Date: | 2021-04-26 17:14:50 |
Message-ID: | CAFj8pRANQAdPNqfZ0c7pESngw3SwuW38rQ-NT+YqmQP4Mv0_gA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
po 26. 4. 2021 v 19:10 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>
> regards, tom lane
>
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2021-04-26 17:19:25 | Re: Issue in recent pg_stat_statements? |
Previous Message | Julien Rouhaud | 2021-04-26 17:14:04 | Re: compute_query_id and pg_stat_statements |