Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create a row-wise conditional view

Create a row-wise conditional view

From
Pedro Tarrafeta
Date:
Hi,

I'm trying to create a set of views like this. Let's say I have 2 tables with the same structure. One of them is the "master" table (left side) and the other one has a different "version" of the fields only for certain rows.

I want to create a view that would have the same structure as the tables, holding all the records from the master table but when there's a record matching on the secundary then use those.

This doesn't work on Postgresql 8.0.3 but is very close to what I need:
CREATE VIEW work_view AS
SELECT CASE WHEN table2.id IS NULL THEN table1.*
       ELSE table2.*
END

I can use COALESCE but this is field after field or compute multiple CASEs where the same condition is evaluated again and again, field after field and row after row...

Any ideas?
Thanks
 
Pedro

Thank you

Re: Create a row-wise conditional view

From
Tom Lane
Date:
Pedro Tarrafeta <ptarra@gmail.com> writes:
> I'm trying to create a set of views like this. Let's say I have 2 tables
> with the same structure. One of them is the "master" table (left side) and
> the other one has a different "version" of the fields only for certain rows.

> I want to create a view that would have the same structure as the tables,
> holding all the records from the master table but when there's a record
> matching on the secundary then use those.

Maybe something like

    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
      WHERE NOT EXISTS(SELECT 1 FROM table1 where id = table2.id);

(This may have table1 and table2 reversed from what you were thinking.)

This is going to be pretty messy and inefficient, though, so I'd really
counsel rethinking your data design.

            regards, tom lane