Re: enum bug

[Elein <elein@xxxxxxxxxxx>]

The point is that enum information belongs to a type, not a column value of that type. 

This is the difference between a class and a class instance. 

If you get that, you understand. 

The workaround  suggested only works if some non-empty row in some table has a column defined to be that enum type. 

An unused (yet) enum type cannot display the enum ranges. An empty table containing that type cannot display enum ranges. 

The example selects were what I did to figure out that enum_ranges only worked on existing data. Sorry if they were confusing. But the way enum values are currently displayed is confusing. 

Enums are evil because of the difficulty working with them. They should be used for a fixed set of valid values. Otherwise use a lookup table. 

Elein Mustain

elein@xxxxxxxxxxx

510-637-9106

On Mar 11, 2016, at 2:06 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Fri, Mar 11, 2016 at 2: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

​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.