load 'pg_relusage';
set client_min_messages to LOG;
---------
-- TABLES
---------
create table test(id serial, t text);
-- we detect 'test' in a simple query
\copy (select * from test) to '/dev/null';
LOG:  relations used: test
-- multiple references reported only once
\copy (select * from test t1 join test t2 using (id)) to '/dev/null';
LOG:  relations used: test
-- this works with built-in tables as well
\copy (select * from pg_class c1 join pg_class c2 using (relname) limit 1) to '/dev/null';
LOG:  relations used: pg_class
-- insert, update, delete statements all covered
insert into test(t) values ('test');
LOG:  relations used: test,test_id_seq
update test set t = t||t;
LOG:  relations used: test
delete from test;
LOG:  relations used: test
--------
-- VIEWS
--------
create view vw_test as select * from test;
-- we detect 'test' as the only relation used here
\copy (select * from vw_test t1 join test t2 using (id)) to '/dev/null';
LOG:  relations used: vw_test,test
-- more complex built-in view
\copy (select * from pg_stats limit 1) to '/dev/null';
LOG:  relations used: pg_stats,pg_statistic,pg_class,pg_attribute,pg_namespace
-------
-- CTEs
-------
\copy (with stats as (select * from pg_stats limit 1) select * from stats) to '/dev/null';
LOG:  relations used: pg_stats,pg_statistic,pg_class,pg_attribute,pg_namespace
------------------------
-- TEMP TABLES AND VIEWS
------------------------
create temp table temp_test(t text);
create temp view vw_temp_test as select * from temp_test;
\copy (select * from vw_temp_test) to '/dev/null';
LOG:  relations used: vw_temp_test,temp_test
----------------
-- SQL functions
----------------
create function test_sql_f(i int) returns text
language sql
as $_$
select t from test where id = i
$_$;
select * from test_sql_f(42);
LOG:  relations used: test
 test_sql_f 
------------
 
(1 row)

-----------
-- PL/pgSQL
-----------
create function test_pgsql_f(t text) returns text
language plpgsql
as $_$
declare
  res text;
begin
select t into res from pg_stats where tablename = t;
return res;
end;
$_$;
select * from test_pgsql_f('foobar');
LOG:  relations used: pg_stats,pg_statistic,pg_class,pg_attribute,pg_namespace
 test_pgsql_f 
--------------
 
(1 row)

-- dynamic queries
create function test_pgsql_dyn_f(t text) returns text
language plpgsql
as $_$
declare
  res text;
begin
execute $$ select schemaname from pg_stats where tablename = $$ || quote_literal(t) into res;
return res;
end;
$_$;
select * from test_pgsql_dyn_f('foobar');
LOG:  relations used: pg_stats,pg_statistic,pg_class,pg_attribute,pg_namespace
 test_pgsql_dyn_f 
------------------
 
(1 row)

------
-- GUC
------
set pg_relusage.log_level = 'INFO';
set pg_relusage.rel_kinds = 'r';
\copy (select * from pg_stats limit 1) to '/dev/null';
INFO:  relations used: pg_statistic,pg_class,pg_attribute,pg_namespace
set pg_relusage.log_level = 'NOTICE';
set pg_relusage.rel_kinds = 'v';
\copy (select * from pg_stats limit 1) to '/dev/null';
NOTICE:  relations used: pg_stats