Re: Is there a reason why Postgres doesn't have Byte or tinyint? - Mailing list pgsql-general

>>>>>>>>> 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?