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