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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
