Re: Declaring constants in SQL

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