Re: enum bug

On Fri, Mar 11, 2016 at 2:36 PM, Elein <elein(at)varlena(dot)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
> elein(at)varlena(dot)com
> 510-637-9106
>
>
​Just reading here...​

​I don't really understand what point you are trying to make but:

SELECT enum_range(color) FROM foo;

enum_range() will be executed once for every row in foo. A row can only
exist in foo if either color is a valid rainbow enum or NULL.

I
​f you simply want to execute enum_range(rainbow) you can do something as
simple as:

SELECT enum_range(null::rainbow);

​I do see that there could be value in having:

​
​
> -- 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');

​add a HINT: to the output listing all labels for the enum

If that is your main point you did a good job of hiding in in a bunch of
other stuff that is only tangentially related. Oth​erwise I still have no
idea what exactly is the point you are trying to make.

What version are you using anyway?

David J.