determining which table to lookup depending on data values

[Steve Castellotti <SteveC@xxxxxxxxxxxx>]

Hello all-

    I'm working with a poorly-designed schema and need to do a lookup in one table who's name I have to pull from a second table. I'm wondering if its possible to do something like this in PostgreSQL:


Say I have three tables:

CREATE TABLE audio (id int4, name varchar(32));
CREATE TABLE video (id int4, name varchar(32));
CREATE TABLE media (id int4, table_name varchar(32), table_id int4);

with data:

INSERT INTO audio VALUES (0, 'file.wav');
INSERT INTO video VALUES (0, 'file.avi');
INSERT INTO media VALUES (0, 'audio', 0);
INSERT INTO media VALUES (1, 'video', 0);


    Is there any way, especially in a single statement, where I can get the name of a few if I only have the media table's id?

SELECT name FROM (SELECT table_name FROM media WHERE media_id=1);

    Of course I get back an error about needing to give my subquery an alias, but even if I do so I only get the output from the subquery, instead of being able to tell PostgreSQL to use that output as the name of the table it should use to look up "name"

    The original design was meant for a multimedia play which could use a mixture of audio and video in a playlist, and that which housed all of the entries for the playlist could just refer to an id for the media table. When the software plays it has to pull this information out and do a single lookup for each item. This looses referential integrity and means you can't get from a play down to a specific file with one query.


Thanks in advance!

Steve Castellotti