Обсуждение: Declaring constants in SQL

Let's say I have a table foo with a column color where the color column
is defined as an integer but in my application space is an enumeration:

                          Table "public.foo"
        Column         |            Type             |       Modifiers

-----------------------+-----------------------------+------------------
------
 fid                   | character varying(10)       | not null
 color                 | integer                     | not null

The enumerated values are enum color_type { RED=1, BLUE=2, YELLOW=3,
GREEN=4, PURPLE=5 }

When I write a function in plpgsql I can declare constants as follows:

CREATE OR REPLACE FUNCTION bar() RETURNS TRIGGER AS
$$
    DECLARE
        FOO_COLOR_RED        CONSTANT    INT    :=    1;
        FOO_COLOR_BLUE        CONSTANT    INT    :=    2;
        FOO_COLOR_YELLOW    CONSTANT    INT    :=    3;
    BEGIN

        -- FOO_COLOR_RED is more legible than 1 --
        IF new.color = FOO_COLOR_RED THEN
               -- some red logic here --
        END IF;

        RETURN new;
    END;
$$ LANGUAGE plpgsql;

Let's say I want a VIEW of all the foo records that have primary colors:

CREATE VIEW primary_color_foos AS
   SELECT * FROM foo WHERE ( color = 1 OR color = 2 OR color = 3 );

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 would like to make my RULEs and VIEWs a little more human readable.

I am using postgreSQL 8.2 that does not directly support the enum data
type.

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

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

The last part got scrambled, should read like this:

(...)
Use it like this:
SELECT * FROM foo WHERE foo_id > myval();

Or, for the case at hand, an example in sql:

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(my_colors());

Regards
Erwin