By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
create schema schemaone;
create schema schematwo;
CREATE FUNCTION schemaone.get_pg_stat_activity() RETURNS SETOF pg_stat_activity
LANGUAGE sql SECURITY DEFINER
AS $$ SELECT * FROM pg_catalog.pg_stat_activity; $$;
| version |
|---|
| PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
CREATE SCHEMA
CREATE SCHEMA
CREATE FUNCTION
--version 10-12
CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
--FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type, "query (2)", "backend_type (2)");
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type)--, "query (2)", "backend_type (2)");
ERROR: column "query" specified more than once
--version 13
CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type, "backend_type (2)");
ERROR: column "backend_type" specified more than once
--version 14-16
CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type, "query (2)", "backend_type (2)");
CREATE VIEW
select p.ordinal_position,
p.column_name as source_column,
v.column_name as your_alias,
p.column_name=v.column_name as is_equal,
case when v.column_name=p.column_name then 0
when v.column_name=lead(p.column_name,1)over() then 1
when v.column_name=lead(p.column_name,2)over() then 2
else 0 end as name_shifted_by
from ( select p.ordinal_position,
p.column_name
from information_schema.columns p
where table_schema='pg_catalog'
and table_name='pg_stat_activity'
order by p.ordinal_position) p
join ( select v.ordinal_position,
v.column_name
from information_schema.columns v
where table_schema='schematwo'
and table_name='pg_stat_activity'
order by v.ordinal_position) v
on p.ordinal_position=v.ordinal_position;
| ordinal_position | source_column | your_alias | is_equal | name_shifted_by |
|---|---|---|---|---|
| 1 | datid | datid | t | 0 |
| 2 | datname | datname | t | 0 |
| 3 | pid | pid | t | 0 |
| 4 | leader_pid | usesysid | f | 1 |
| 5 | usesysid | usename | f | 1 |
| 6 | usename | application_name | f | 1 |
| 7 | application_name | client_addr | f | 1 |
| 8 | client_addr | client_hostname | f | 1 |
| 9 | client_hostname | client_port | f | 1 |
| 10 | client_port | backend_start | f | 1 |
| 11 | backend_start | xact_start | f | 1 |
| 12 | xact_start | query_start | f | 1 |
| 13 | query_start | state_change | f | 1 |
| 14 | state_change | wait_event_type | f | 1 |
| 15 | wait_event_type | wait_event | f | 1 |
| 16 | wait_event | state | f | 1 |
| 17 | state | backend_xid | f | 1 |
| 18 | backend_xid | backend_xmin | f | 1 |
| 19 | backend_xmin | query | f | 2 |
| 20 | query_id | backend_type | f | 2 |
| 21 | query | query (2) | f | 0 |
| 22 | backend_type | backend_type (2) | f | 0 |
SELECT 22
CREATE VIEW schematwo.pg_stat_activity2 AS
SELECT
get_pg_stat_activity.datid,
get_pg_stat_activity.datname,
get_pg_stat_activity.pid,
get_pg_stat_activity.usesysid,
get_pg_stat_activity.usename,
get_pg_stat_activity.application_name,
get_pg_stat_activity.client_addr,
get_pg_stat_activity.client_hostname,
get_pg_stat_activity.client_port,
get_pg_stat_activity.backend_start,
get_pg_stat_activity.xact_start,
get_pg_stat_activity.query_start,
get_pg_stat_activity.state_change,
get_pg_stat_activity.wait_event_type,
get_pg_stat_activity.wait_event,
get_pg_stat_activity.state,
get_pg_stat_activity.backend_xid,
get_pg_stat_activity.backend_xmin,
get_pg_stat_activity.query,
get_pg_stat_activity.backend_type
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, leader_pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query_id, query, backend_type);
CREATE VIEW
CREATE VIEW schematwo.pg_stat_activity3 AS
SELECT
get_pg_stat_activity.datid,
get_pg_stat_activity.datname,
get_pg_stat_activity.pid,
get_pg_stat_activity.usesysid,
get_pg_stat_activity.usename,
get_pg_stat_activity.application_name,
get_pg_stat_activity.client_addr,
get_pg_stat_activity.client_hostname,
get_pg_stat_activity.client_port,
get_pg_stat_activity.backend_start,
get_pg_stat_activity.xact_start,
get_pg_stat_activity.query_start,
get_pg_stat_activity.state_change,
get_pg_stat_activity.wait_event_type,
get_pg_stat_activity.wait_event,
get_pg_stat_activity.state,
get_pg_stat_activity.backend_xid,
get_pg_stat_activity.backend_xmin,
get_pg_stat_activity.query,
get_pg_stat_activity.backend_type
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity;
CREATE VIEW
CREATE VIEW schematwo.pg_stat_activity4 AS
SELECT * FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(datid, datname, pid, leader_pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query_id, query, backend_type);
CREATE VIEW
CREATE VIEW schematwo.pg_stat_activity5 AS
SELECT * FROM schemaone.get_pg_stat_activity();
CREATE VIEW