Re: Declaring constants in SQL

On Jul 30, 10:53 pm, richard(dot)broer(dot)(dot)(dot)(at)gmail(dot)com ("Richard Broersma")
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <Peter(dot)M(dot)Rother(dot)(dot)(dot)(at)boeing(dot)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