Get the table name of each row from inherited tables [duplicate]

Question

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.

Answer 1

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