Re: help - Mailing list pgsql-general

Yes. I know that using '' defines the field as a
string so I get the TEXT cast. Let me try to show you
why I need it in ''. I default my parameters in my
application to empty strings. They don't need to be
casted so I can override them at any time with an
integer value, string, boolean, float, etc. So I
typically will defalut value = ''

value = ''
nullif($value,'') // evaluates nullif(,''). ERROR.

// override value now with integer
value = 4
nullif('$value','') // evaluates nullif('4',''). Now
we add 4 to int column

// override again
value = ''
nullif('$value','') // evals to nullif('',''). Now we
know it's NULL so let's insert NULL

In MS SQL (no m$ - you like how they patented XML &
tried patenting IPv6? Oh and tabbed browsing. Patents
are a joke and hinder innovation) evaluats
nullif('4','') as an INT 4 or nullif('','') returns
any NULL -- no matter the column inserted into.

Sorry if I'm being difficult or pestering. I just want
to use postgres for my applications database and know
I can get this working.

All I need is integer columns evaluting in a

nullif('1','') returns int = 1
nullif('','') returns int = null
nullif('0','') returns int = 0

fashion upon inserting to an INTEGER column. Forget
booleans. Forget text. Just integers. Is this
possible? Nothing is impossible. Tell me it can be
done!

Could I use a C++ function to do what I'm trying to
accomplish? Or is there an more elegant solution?
(like a built in sql one)

Thanks


--- Martijn van Oosterhout <kleptog@svana.org> wrote:

> On Thu, Aug 25, 2005 at 04:10:27PM -0700, Matt A.
> wrote:
> > Anyway, I am new to postgres and looking to casts.
> I
> > have wrote this question several times before and
> on
> > this version I forgot to add the exact rules of
> why
> > it's breaking and what i need it to do. I will try
> to
> > be more clear...
> >
> > I use the rules to insert 0/1/null inserts into
> > booleans, integers, text, etc. fields.
> >
> > Example: insert into table (bool_column) values
> > nullif('',''));
> > ERROR: column "bool_column" is of type boolean but
>
> > expression is of type text.
>
> First rule, the type of a functions is determined by
> its arguments.
> Neither of the arguments in your example are boolean
> so it doesn't know
> you want a boolean. nullif itself works on any type.
>
> kleptog=# create temp table x (t text, b bool, i
> integer);
> CREATE TABLE
> kleptog=# insert into x values (nullif('1','1'),
> nullif(true,true),
> nullif(4,4) );
> INSERT 114774 1
> kleptog=# select * from x;
>  t | b | i
> ---+---+---
>    |   |
> (1 row)
>
> All nulls...
>
> Your choice are to indicate in the arguments what
> type you want. In
> this case the arguments arn't booleans so that won't
> work. Your other
> option is to cast the result, which you can't
> because text -> bool is
> not a valid typecast.
>
> >
> > Where '' == '' should evaluate to NULL as OPAQUE
> > (depreciated) or similiar return instead of TEXT
> cast.
> >
> > So if I could alter the cast from text to return
> > either INTEGER or TEXT, that would super! I'm not
> sure
> > if it's possible but if so I'm willing to do what
> it
> > takes to accomplish it.
>
> Functions don't return OPAQUE, they can't because a
> function knows
> exactly what it's returning. In your case it's
> returning TEXT because
> that's what the arguments default to if it doesn't
> know any better.
>
> Technically, nullif takes arguments
> (anyelement,anyelement) which means
> it can take any type, as long as they're the same.
>
> Hope this clarifies it for you.
> --
> Martijn van Oosterhout   <kleptog@svana.org>
> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95%
> perspiration. A patent is a
> > tool for doing 5% of the work and then sitting
> around waiting for someone
> > else to do the other 95% so you can sue them.
>




____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs