Returning table name and schema name along with column values

Question

I have table in SQL:

public.employee

name	age
abc	20
xyz	30

I want a query which would return the column values along with schema name and table name.

Input:

select * from public.employee

Output:

name	age	schema_name	table_name

The reason for doing this is, I want to have an aggregate info table which has count for each schema and table.

Answer 1

You can display the table name in the result using the system column tableoid that is available in every table.

select *, tableoid::regclass::text as table_name
from public.employee;

This will however only display the fully qualified name if the table's schema is not in the search_path. If the table's schema is in the search_path, only the table name will be shown.