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

-----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-----