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