Declaring constants in SQL

Поиск
Список
Период
Сортировка
От EXT-Rothermel, Peter M
Тема Declaring constants in SQL
Дата
Msg-id 8D9E4E8445BD14478121CC9B027B518AB57294@XCH-NW-11V2.nw.nos.boeing.com
обсуждение исходный текст
Ответы Re: Declaring constants in SQL  ("Richard Broersma" <richard.broersma@gmail.com>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Ismael ...."
Дата:
Сообщение: Alternative way to access a field in a %ROWTYPE variable
Следующее
От: "Richard Broersma"
Дата:
Сообщение: Re: Declaring constants in SQL