>>>>>>>>>>> According to the manuals, Postgres has smallint (2 byte), integer (4 >>>>>>>>>>> bytes) and bigint (8 bytes).. I use a lot of structures with "bytes" >>>>>>>>>>> in my code and it's kinda annoying to cast DB output from Int16 to >>>>>>>>>>> Byte every time, especially since there's no explicit cast in .NET and >>>>>>>>>>> you have to use System.Convert(). >>>>>>>>>>> >>>>>>>>>>> Is there a work-around, or do people just cast or use Int16 in their >>>>>>>>>>> data structures? Just wondering.. I know on modern computers it >>>>>>>>>>> probably doesn't make any difference anyway.. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Is this just about programmer convenience or is it about space efficiency >>>>>>>>>> in >>>>>>>>>> the database? BYTEA might help you. Or try declaring a DOMAIN over >>>>>>>>>> SMALLINT that limits allowed values to the range of a byte. -- Darren >>>>>>>>>> Duncan >>>>>>>>> >>>>>>>>> >>>>>>>>> This is purely programmer convenience. >>>>>>>>> >>>>>>>>> Basically, I want Npgsql to marshal the value as a .NET Byte type, if >>>>>>>>> I can find a way to do that I'm happy. Perhaps it's more of a Npgsql >>>>>>>>> question, though I'm curious as to why Postgres doesn't have an >>>>>>>>> intrinsic tinyint or byte type. >>>>>>>> >>>>>>>> >>>>>>>> Maybe Postgres doesn't need a Byte type predefined because it gives you the >>>>>>>> means to define the type yourself, such as using DOMAIN. >>>>>>>> >>>>>>>> Generally speaking, I believe it is more important for a type system to >>>>>>>> provide the means for arbitrary user-defined types which can be used in all >>>>>>>> the places as built-in-defined types, than to have large numbers of >>>>>>>> built-in-defined types. >>>>>>> >>>>>>> Precisely. postgresql's extensable nature allows you to build your >>>>>>> own types as well. If it's popular enough it'll make it into contrib, >>>>>>> then maybe core. My guess is that there's some non-trivial cost to >>>>>>> maintaining each core type, and since a byte type isn't required by >>>>>>> the SQL spec, it would take some effort to get a standard one included >>>>>>> in the core. >>>>>> >>>>>> That makes sense. >>>>>> >>>>>> I guess my question is more of a NpgSql question then. Is there a way >>>>>> to create a custom PG type, and have npgsql serialize that type in a >>>>>> dataset to a .NET Byte type? >>>>>> >>>>>> I'd probably be better off posting on the npgsql mailing list, but >>>>>> perhaps someone here knows as well.. >>>>>> >>>>> >>>>> >>>>> Hi! >>>>> >>>>> Npgsql already has support to map .Net byte data type to int2 and vice-versa. >>>>> >>>>> You can see all the supported type mappings in this file: >>>>> http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/NpgsqlTypes/NpgsqlTypesHelper.cs?rev=1.41&content-type=text/x-cvsweb-markup >>>>> >>>>> Check method PrepareDefaultTypesMap(). >>>>> >>>>> I hope it helps. >>>> >>>> Thanks! I'll have to mess around with this a bit more.. From what >>>> I've seen so far, functions that have int2 out parameters will return >>>> Int16 through the DataReader.. Maybe I'm doing something wrong.. >>>> >>>> Mike >>> >>> >>> I think you aren't doing anything wrong. int2 postgresql datatypes are >>> supposed to be mapped to .net int16 types. >>> >>> Do you have a simple example? >>> >>> What type were you expecting in the datareader? Maybe there is a >>> missing mapping in Npgsql. >>> >>> Thanks in advance. >> >> I'm pretty sure your code is working the way it's designed. If I pass >> /in/ a .NET Byte type, it's compatible with an Int2 (16bit int). It's >> basically "cast-up" to the smallest intrinsic type Postgres supports. >> However, data coming back out from PG is where I run into the issue. >> What I'm doing is calling a function that has the following signature: >> >> CREATE FUNCTION KPC_GetMealPlans(IN _category MealPlanCategoryEnum, >> OUT id uuid, OUT title character varying, OUT imageurl character >> varying, OUT rating smallint, OUT numratings integer, out crediturl >> character varying, OUT recipecount integer, out ingredientcount >> integer) >> RETURNS SETOF record AS >> $BODY$ >> BEGIN >> -- All sorts of stuff way too mind-blowing for this email >> END; >> >> Then I call it as so: >> >> IDbCommand cmd = session.Connection.CreateCommand(); >> cmd.CommandType = CommandType.StoredProcedure; >> cmd.CommandText = "KPC_GetMealPlans"; >> //...bind parameter >> return cmd.ExecuteReader(); >> >> This will return an iDataReader, which I loop through. That >> IDataReader will return an Int16 type for the Rating OUT parameter, >> which I'm pretty sure is by default. However, I would like it to >> return a Byte, because I use a Byte for all my ratings (which are a >> number of stars for the recipe, between 0 and 5).. >> >> If I understand correctly, I can create a Postgres domain called >> "Rating" as well which would be even cooler. However, how would I >> then tell Npgsql to marshal that back as either a Byte or even my own >> Rating .NET type? > > > > > Got it. > > Sorry if I rushed in my response. In fact Npgsql does the conversion > from .Net byte to int2 but not the other way around :( > > I have in my plans since a long time ago to implement custom type > conversions so users could solve problems like yours with custom types > on postgresql database and .net. But for while I didn't worked on > anything about that. > > I see 2 possible solutions for you right now: > > 1. Add your custom types directly in Npgsql code by adding custom > typeconverters and compile Npgsql. > > 2. Try to use a Bit datatype. Npgsql has a BitString datatype which > maps to postgresql bit fields which could give you the information you > want. But I don't know if it would be too much overkill for your > rating system. > BitString datatype already have a lot of helpers methods though > including one which translates the bitstring to a byte value which I > think would be helpful to you. Thanks so much for your help! I bet the BitString (this is basically a representation of a bitmask, I take it) would work perfectly for representing a 0-5 rating, plus maybe a bit at the end for representing the half star or something. However, I think it's a bit overkill at the moment when the alternative is just cast an Int16 to a Byte, or just use an Int16 to represent the rating in the first place. Creating a customized version of the source equally so, though I'd love to dig into the source when I have some free time (oh wait, KitchenPC /is/ my free time).. I cannot say +1 enough to the idea of implementing custom type conversions though - Even if it's a quick and dirty solution. I would definitely love the idea of Npgsql converting database ENUMs to my own C# matching enums, or DOMAINs to instances of my own C# classes (such as the Rating class). I already do a little bit of this through NHibernate, and it works pretty well, but it only works through the ORM and not when I query directly. If you ever have a free weekend of a long flight to get something working, I'll owe you a drink and be the first in line to thoroughly test it for you! Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
