On Fri, Mar 11, 2016 at 4:36 PM, Elein <elein@xxxxxxxxxxx> wrote:
> Bug/Improvement:
>
> Enums belong to types, not to column values.
>
> * Create a built in function, like enum_range( type ), independent of tables
> that shows a type's enum values.
> create or replace function enum_range( typein regtype ) returns name[] language sql as
> $$
> select array_agg(enumlabel) from
> (select enumlabel
> from pg_enum e join pg_type t on (e.enumtypid = t.oid)
> where t.typtype = 'e' and t.oid = typein
> order by enumsortorder
> ) foo;
> $$;
> * When an insert into an enum column fails give the person a hint as to valid values
> * Make enum_range to not be dependent on values in the target table.
> Remove/Obsolete enum_range( enum_column ) and replace with enum_range( typein regtype )
>
> Workaround: define the enum_range( typein regtyp ) yourself.
>
>
> create type rainbow as enum ('red','orange','yellow','blue','purple');
> CREATE TYPE
> create table badinfo (color rainbow);
> CREATE TABLE
>
> -- Lousy message. Show enum list.
> insert into badinfo values ('green');
> ERROR: invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');
>
>
> -- Lousy message.
> select enum_range(color) from foo;
> enum_range
> ------------
> (0 rows)
>
> insert into foo values ('red');
> INSERT 0 1
> insert into foo values ('blue');
> INSERT 0 1
>
> -- Ooh. Only shows the enum list for valid value in a table.
> select enum_range(color) from foo;
> enum_range
> ---------------------------------
> {red,orange,yellow,blue,purple}
> {red,orange,yellow,blue,purple}
> (2 rows)
Elein Mustain
elein@xxxxxxxxxxx
510-637-9106
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Enums are evil!
http://www.lornajane.net/posts/2010/is-enum-evil
enums are from before there were foreign keys
You are better off just making a foreign key contraint.
Things will be a lot simpler.
eg:
CREATE TABLE rainbow_colors
( valid_color varchar(15),
CONSTRAINT rainbow_colors_pk PRIMARY KEY(valid_color)
);
CREATE TABLE badinfo
( color varchar(15),
CONSTRAINT badinfo_pk PRIMARY KEY (color),
CONSTRAINT badinfo_valid FOREIGN KEY (color)
REFERENCES rainbow_colors (valid_color)
);
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
