4

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.

Kevin Orriss
  • 471
  • 1
  • 4
  • 13
  • 3
    Does this answer your question? [Get the name of a row's source table when querying the parent it inherits from](https://stackoverflow.com/questions/24580352/get-the-name-of-a-rows-source-table-when-querying-the-parent-it-inherits-from) – Pipo Jul 26 '20 at 14:41

1 Answers1

9

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

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • I can see where this would fall apart yes. It would seem then that postgres' "inheritance" features are very limited then in comparison to C# for example. Thanks for the reply! – Kevin Orriss Apr 01 '14 at 10:38
  • Yeah, Pg table inheritance is pretty limited. It's also quirky. In particular you can't have a unique constraint that spans all members of an inheritance tree, or a foreign key that references rows that may appear in any child of a parent table. – Craig Ringer Apr 01 '14 at 13:09