Re: Declaring constants in SQL

On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
<Peter(dot)M(dot)Rothermel(at)boeing(dot)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