Column is ambiguous

Question

CREATE FUNCTION schemaone.get_pg_stat_activity() RETURNS SETOF pg_stat_activity
    LANGUAGE sql SECURITY DEFINER
    AS $$ SELECT * FROM pg_catalog.pg_stat_activity; $$;
    
CREATE VIEW schematwo.pg_stat_activity 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, 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);

I have these queries here; I get the following error when I try using pg_restore.

10:33:56 1251111 ERROR string_utils.c:650
pg_restore: error: could not execute query: ERROR: column reference "query" is ambiguous
10:33:56 1251111 ERROR string_utils.c:650
LINE 20: get_pg_stat_activity.query,

Answer 1

You are probably trying to re-use PostgreSQL version 10-12 definition of pg_stat_activity in your alias, while being on version 14+ so the alias list doesn't match the names and amount of output columns, leaving you with 2 stray, unaliased columns causing the conflict. Demo:

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

4th column of pg_stat_activity is leader_pid and by ommitting it in your list, you're still selecting it but renaming as usesysid and shifting all the column names that follow, one up/to the left. 20th column is query_id that you also skipped, so at the end, you're still selecting everything that comes out of pg_stat_activity through your schemaone.get_pg_stat_activity() function, but renaming columns 4-20, then leaving the last two unaliased, which happen to be query and backend_type.

So the last 4 columns that result from this:

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);

are query, backend_type, query, backend_type, causing the conflict.

You need to make your aliases match:

CREATE VIEW schematwo.pg_stat_activity2 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);

or remove that list entirely

CREATE VIEW schematwo.pg_stat_activity AS
SELECT *
FROM schemaone.get_pg_stat_activity() AS get_pg_stat_activity;