Thread: getting inherited table name

In the pgsql tutorial two tables are created capitals inherits  cities.

When you do SELECT * FROM cities, you get both capitals and cities.  Is
there anyway to get get the name of the table so I could possibly know
the 'type' it was? Or should this be maintained as a separate column
'city_type' that has a value of 'capital'?

Suppose I had another table river_cities and what I would want to be
able to is SELECT * FROM cities and know whether the city was a
river_city, capital, or nothing at all.

thanks,
--eric

Eric Kolve writes:

> When you do SELECT * FROM cities, you get both capitals and cities.  Is
> there anyway to get get the name of the table so I could possibly know
> the 'type' it was? Or should this be maintained as a separate column
> 'city_type' that has a value of 'capital'?

There's an otherwise hidden column called "tableoid" that contains the oid
of the table the row really came from.  You can join that against pg_class
to get the name of the table.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Eric Kolve wrote:

> In the pgsql tutorial two tables are created capitals inherits  cities.
>
> When you do SELECT * FROM cities, you get both capitals and cities.  Is
> there anyway to get get the name of the table so I could possibly know
> the 'type' it was? Or should this be maintained as a separate column
> 'city_type' that has a value of 'capital'?
>
> Suppose I had another table river_cities and what I would want to be
> able to is SELECT * FROM cities and know whether the city was a
> river_city, capital, or nothing at all.


No additional fields are required:

SELECT  c.*, c.tableoid, pgc.relname as city_type
FROM cities c, pg_class pgc
WHERE c.tableoid = pgc.oid


regards
 Nico