On Jul 30, 10:53 pm, richard.broer...@gmail.com ("Richard Broersma")
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <Peter.M.Rother...@boeing.com> wrote:
> > Is there any means like (#define or DECLARE ) where I can write SQL like
(...)
> CREATE VIEW primary_colors_foos AS
> SELECT * FROM foo
> WHERE color = ANY( SELECT colorid
> FROM Colors
> WHERE colorname = ANY( 'red', 'blue', 'yellow' ));
Or even:
CREATE VIEW primary_color_foos AS
SELECT foo.* FROM foo JOIN color c USING (color_id)
WHERE c.colorname IN ('red', 'blue', 'yellow' );
If you have some constant values you need all over the place, you can
also resort to functions, which you can use much like CONSTANTs:
CREATE FUNCTION my_val()
RETURNS integer AS
$BODY$
BEGIN
RETURN 21;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Or, for the case at hand, an example in sql:
SELECT * FROM foo WHERE foo_id > myval();
CREATE FUNCTION my_colors()
RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
LANGUAGE 'sql' IMMUTABLE;
Use it like this:
SELECT * FROM foo WHERE color = ANY(myval());
Regards
Erwin