Re: Declaring constants in SQL - Mailing list pgsql-general

On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
<Peter.M.Rothermel@boeing.com> wrote:

> Is there any means like (#define or DECLARE ) where I can write SQL like
> this:
> CREATE VIEW primary_colors_foos AS
>   SELECT * from foo WHERE ( color = FOO_COLOR_RED OR color =
> FOO_COLOR_BLUE OR color = FOO_COLOR_YELLOW );

I don't think that you can declare SQL variables like this.  The
conventional method would be to use a look up table that hold the
cross-reference between integer and color name.  You could then change
your view definitional like so:

CREATE VIEW primary_colors_foos AS
  SELECT * FROM foo
   WHERE color = ANY( SELECT colorid
                        FROM Colors
                       WHERE colorname = ANY( 'red', 'blue', 'yellow' ));

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug