On Tue, Jan 10, 2012 at 00:36, Mike Christensen <mike@xxxxxxxxxxxxx> wrote: >>>>>>>>>> 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. I hope it helps. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://gplus.to/franciscojunior http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
