Re: enum bug - Mailing list pgsql-general

On Fri, Mar 11, 2016 at 2:36 PM, Elein <elein@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
elein@varlena.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.