Thread: npgsql and postgres enum type

Hi,

As postgres now has enum type, does npgsql driver support the enum type?

I use c# and npgsql as databse driver.  One of the database stored procedure
takes enum as its parameter.

What will be the DbType for postgres enum type?

Thanks /dan
--
View this message in context: http://www.nabble.com/npgsql-and-postgres-enum-type-tp25911871p25911871.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, Oct 15, 2009 at 12:31 PM, danclemson <danclemson@gmail.com> wrote:
>
> Hi,
>
> As postgres now has enum type, does npgsql driver support the enum type?
>
> I use c# and npgsql as databse driver.  One of the database stored procedure
> takes enum as its parameter.
>
> What will be the DbType for postgres enum type?

that's really a npgsql question, but as long as you have access to the
sql being used, you should be able to work around it by altering the
sql like this:

select some_function('abc'::the_enum);

merlin

2009/10/15 Merlin Moncure <mmoncure@gmail.com>:
> On Thu, Oct 15, 2009 at 12:31 PM, danclemson <danclemson@gmail.com> wrote:
>>
>> Hi,
>>
>> As postgres now has enum type, does npgsql driver support the enum type?
>>
>> I use c# and npgsql as databse driver.  One of the database stored procedure
>> takes enum as its parameter.
>>
>> What will be the DbType for postgres enum type?
>
> that's really a npgsql question, but as long as you have access to the
> sql being used, you should be able to work around it by altering the
> sql like this:
>
> select some_function('abc'::the_enum);
>
> merlin
>

I recently stopped using enums after reading this:
http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/
Using a foreign key to a single column table is pretty much as fast as
an enum, is supported by most (all?) third party libraries, and avoids
all the problems associated with enums.
I guess the downside is the foreign key will take up more disk space,
but that isn't an issue for me.

Cheers, Will Temperley.

On Thu, Oct 15, 2009 at 2:52 PM, William Temperley > I recently
stopped using enums after reading this:
> http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/
> Using a foreign key to a single column table is pretty much as fast as
> an enum, is supported by most (all?) third party libraries, and avoids
> all the problems associated with enums.
> I guess the downside is the foreign key will take up more disk space,
> but that isn't an issue for me.

enums are a bit faster in the general case: you have a oid's worth of
storage.  where enums have the chance to pay big dividends is indexes
_espeically_ if the enum is part of more complex ordering.  This can
be worked around using the classic approach but the enum is simpler
and cleaner.

For example, suppose you have a requirement you have to pulling up
orders by account#/status

select *  from order where .. order by account_id, status ;

if the status is an enum, you can take advantage of the enum's natural
ordering without the performance killing join for the natural ordering
or using function tricks in the create index statement to get good it
working properly.

This case comes often enough to justify enum's existence IMO.

merlin

Thanks for the information.

I did try the approach, but it failed due to any other issue with npgsql.

The stored procedure returns a setof refcursor.
If I use "select * from test('e1':testEnum)", the command.ExecuteReader does
not return the datareader properly. The code errored out when I use the
datareader to get the data in the refcursor.

The stored procedure (return setof refcursor) works if I use prepared
statement, but in this case I am unable to do the explict type cast.
--
View this message in context: http://www.nabble.com/npgsql-and-postgres-enum-type-tp25911871p25915268.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, Oct 15, 2009 at 4:14 PM, danclemson <danclemson@gmail.com> wrote:
>
> Thanks for the information.
>
> I did try the approach, but it failed due to any other issue with npgsql.
>
> The stored procedure returns a setof refcursor.
> If I use "select * from test('e1':testEnum)", the command.ExecuteReader does
> not return the datareader properly. The code errored out when I use the
> datareader to get the data in the refcursor.
>

if you can't figure out any other solution (there probably is one),
wrap your procedure in sql function that takes text and do the casting
there.

merlin

On Thu, Oct 15, 2009 at 19:01, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Oct 15, 2009 at 4:14 PM, danclemson <danclemson@gmail.com> wrote:
>>
>> Thanks for the information.
>>
>> I did try the approach, but it failed due to any other issue with npgsql.
>>
>> The stored procedure returns a setof refcursor.
>> If I use "select * from test('e1':testEnum)", the command.ExecuteReader does
>> not return the datareader properly. The code errored out when I use the
>> datareader to get the data in the refcursor.
>>
>
> if you can't figure out any other solution (there probably is one),
> wrap your procedure in sql function that takes text and do the casting
> there.
>




I just saw this thread now...

If you want to use enum with Npgsql, you just set the type of your
parameter to DbType.Object and Npgsql won't provide any cast.

This way your query will be sent only with quotes which will allow
postgresql to do the proper handling.


I hope it helps.



--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior
Sent from Brasilia, DF, Brazil