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
|
| Список | 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 по дате отправления: