On Fri, Mar 11, 2016 at 4:36 PM, Elein <el...@varlena.com> 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
> el...@varlena.com
> 510-637-9106
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> 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.
