Get the table name of each row from inherited tables
postgresql
Solution
You can use the `tableoid` hidden column for this.
SELECT tableoid, * FROM users;
or in this case:
SELECT tableoid = 'admins'::regclass AS is_admin, * FROM users;
Note, however, that this will fall apart horribly if you want to find a non-leaf membership, i.e. if there was `superusers` that inherited from `admins`, a superuser would be reported here with `is_admin` false.
AFAIK there's no test for "is member of a relation or any child relation(s)", though if you really had t you could get the oids of all the child relations with a subquery, doing a `tableoid IN (SELECT ...)`.
Problem
Take the following query: ``` CREATE TEMP TABLE users ( user_id SERIAL, name varchar(50) ); CREATE TEMP TABLE admins ( section integer ) INHERITS(users); INSERT INTO users (name) VALUES ('Kevin'); INSERT INTO admins (name, section) VALUES ('John', 1); CREATE FUNCTION pg_temp.is_admin(INTEGER) RETURNS BOOLEAN AS $$ DECLARE result boolean; BEGIN SELECT INTO result COUNT(*) > 0 FROM admins WHERE user_id = $1; RETURN result; END; $$ LANGUAGE PLPGSQL; SELECT name, pg_temp.is_admin(user_id) FROM users; ``` Is there any postgres feature that would allow me to get rid of the is_admin function? Basically to check the row class type (in terms of inheritance)? I understand the table design isn't ideal, this is just to provide a simple example so I can find out if what I am after is possible.