getting inherited table name

getting inherited table name

Post by Eric Kol » Fri, 07 Sep 2001 22:14:33



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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

getting inherited table name

Post by Nico » Sat, 08 Sep 2001 02:11:34



> 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

 
 
 

1. Inherited tables vs non-inherited tables

I've been trying an inherited-table schema for my database and seem to
be getting a performance hit. The old table looked like this:

CREATE TABLE center_out (
    subject                     text,
    arm                         char,
    target                      int4,
    rep                         int4,
    success                     int2,       -- end of primary key
    exp_date                    date,
    exp_time                    time,  
    inter_target_radius         int4,
    target_radius               int4);

Since the fields subject, arm, target, and rep appeared in just about
every other table as the primary key, I made it an inherited table for
the new schema:

CREATE TABLE center_out (
        subject    text,
        arm        char,
        target     int2,
        rep        int4,
        success    int2    
        );

CREATE UNIQUE INDEX pkcenter_out ON center_out (subject, arm, target,
rep, success);

CREATE TABLE center_out_behavior (
    exp_date                     date,
    exp_time                     time,
    inter_target_radius          int2,
    target_radius                int2
) INHERITS (center_out);

However, queries such as "SELECT DISTINCT subject FROM center_out"
seem to take 2-3 times longer in the new schema. Does this make sense?
I was hoping that the inherited information would lead to an
easier-to-follow, smaller, and faster database since there wouldn't be
redundant information across tables.

Thanks.
-Tony

 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

2. DTS ActiveXScript Size Limitation / Repository

3. Getting table names without actually knowing their names...

4. Problems with shared memory

5. sqlturbo connection gets inherited

6. shrink database

7. rowcount gets inherited by triggers which causes orphaned rows

8. Frustrating cumulative stats problem...

9. Getting table and column names through Visual Basic.

10. Getting Name of Tables At Run-time?

11. Getting table names

12. Getting table names from a database

13. Getting trigger names on a table