Thread: VIEW AS SELECT -vs- INSERT INTO table query

I have a table of items with about 50 columns, one of which is 'owner'.  I
would like to run several queries where the items for several 'owners' are
to be treated as one owner.  I have considered several options, but they
all have problems:

1) Create a view.
    CREATE VIEW itemcombo AS SELECT 'Combined', [columns] FROM items WHERE
owner IN (...);
where [columns] is everything *except* owner?  Is there a syntax for this
command that would avoid typing all 50 columns?  If I add a new column to
underlying table, then view needs to be replaced?  Is it possible to create
this view in such a way that it could handle "normal" queries of the
underlying table as well (where owner <> 'Combined')?

2) Create a new owner 'Combined' and use "INSERT INTO table query" to
populate items under this owner.
    INSERT INTO items SELECT 'Combined', [columns] FROM items WHERE owner IN
(...);
Again, we have a problem with all those columns.  Also, this command needs
to be rerun whenever underlying table is modified (which, luckily, is not
often).

3) Push the problem to user code.  Every query contains "WHERE owner IN
(...)" instead of "WHERE owner='Combined'".

Are there alternate solutions that I've missed?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I have a table of items with about 50 columns, one of which is 'owner'.  I
> would like to run several queries where the items for several 'owners' are
> to be treated as one owner.  I have considered several options, but they
> all have problems:
>
> 1) Create a view.
>        CREATE VIEW itemcombo AS SELECT 'Combined', [columns] FROM items WHERE
> owner IN (...);
> where [columns] is everything *except* owner?  Is there a syntax for this
> command that would avoid typing all 50 columns? If I add a new column to
> underlying table, then view needs to be replaced?  Is it possible to create
> this view in such a way that it could handle "normal" queries of the
> underlying table as well (where owner <> 'Combined')?

No, yes, and not easily (see below)

> 2) Create a new owner 'Combined' and use "INSERT INTO table query" to

Too much trouble.

> 3) Push the problem to user code.  Every query contains "WHERE owner IN
> (...)" instead of "WHERE owner='Combined'".

This is probably the best place to put this, as all your are really asking for
is a subset of data in the table, and this seems like a perfect candidate for
the IN clause. Unless there is a particular reason not to use this approach,
go with it.

There are some other approaches, such as creating groups of owners, but we'd
have to know more about your system first to determine if it would be
overkill. Try the IN first.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200511121831
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDdnvWvJuQZxSWSsgRAlQ/AJsHkOYEMPIDxrNI5Hd/vCY/TLFy3gCfVV9X
0x+L9wgjG9PSqnPHiVOTFsg=
=EGlI
-----END PGP SIGNATURE-----