Обсуждение: DateDiff, IsNull?

Hello all,
 I am a newbie to postgreSQL, is it possible to write a "DateDiff",
"IsNull" function work same as the one in SQL Server and are there and
sample out there? And is it possible to override the existing function and
operator like "+" to become a concate?

Regards
Bill

On Tue, 14 Aug 2001, Bill wrote:

> Hello all,
>
>   I am a newbie to postgreSQL, is it possible to write a "DateDiff",
> "IsNull" function work same as the one in SQL Server and are there and
> sample out there? And is it possible to override the existing function and
> operator like "+" to become a concate?

I don't know what IsNull does but:

template1=> select coalesce(NULL, 'Hello');case
-------Hello
(1 row)

I do know what DateDiff does:

template1=> select '2001-08-14'::date - '6 weeks'::interval;       ?column?
------------------------2001-07-03 00:00:00-05
(1 row)

Overloading operators? *Please* tell me that this is impossible.

Alan Gutierrez

Alan Gutierrez <alangutierrez@hotmail.com> writes:
> On Tue, 14 Aug 2001, Bill wrote:
>> And is it possible to override the existing function and
>> operator like "+" to become a concate?

> Overloading operators? *Please* tell me that this is impossible.

I've got bad news ;-) ... it's pretty easy.

regression=# select 'aa'::text + 'bb'::text;
ERROR:  Unable to identify an operator '+' for types 'text' and 'text'       You will have to retype this query using
anexplicit cast
 

regression=# create operator + (procedure = textcat,
regression(# leftarg = text, rightarg = text);
CREATE

regression=# select 'aa'::text + 'bb'::text;?column?
----------aabb
(1 row)

Whether this is a good idea is another question --- but if Bill's
intent on not using the SQL-standard text concatenation operator "||",
he can do so.
        regards, tom lane

Alan Gutierrez <alangutierrez@hotmail.com> writes:

[...] 
> Overloading operators? *Please* tell me that this is impossible.

yes, it is :-)

masm=# create function concat (text,text) returns text as 'select $1 || $2 as result' language 'sql' with
(iscachable);
CREATE
masm=# create operator + (procedure = concat, leftarg = text, rightarg = text);
CREATE
masm=# select 'Manuel '+'Sugawara';   ?column?     
-----------------Manuel Sugawara
(1 row)

masm=#

On Tue, Aug 14, 2001 at 06:51:33AM +0000, Alan Gutierrez wrote:
> 
> Overloading operators? *Please* tell me that this is impossible.

Well, Alan, overloading operators is sort of important to the user
definable types in postgresql. And any cross-type functionality, actually.

Bill's original question was looking for 'text1' + 'text2' to mean
concatentation. The right (SQL92) spelling for that is:

'text1' || 'text2'

Currently, since there is no '+' operator for the types 'text' and
'text', you probably could define it as a synonym for ||. I wouldn't
expect your code to be very portable that way, but it might work. (BTW,
as written above, with string constants (type 'unknown'), the type
promotion system kicks in and turns the strings into 'char', I believe,
which it then adds the raw values of the first character, and produces
'�' (sorry about the 8-bit character))

Check the manuals for 'CREATE OPERATOR', then grovel through the
pg_operator table to figure out what all you'd need to hand it for
procedure names, etc.

Ross

Bill,

>   I am a newbie to postgreSQL, is it possible to write a "DateDiff",
> "IsNull" function work same as the one in SQL Server and are there
> and
> sample out there?

I take it that you want to do this to port an exisiting application?

If so, the DateDiff thing has already been covered in Roberto Mello's
"cookbook".

However, ISNULL, last I checked, was a Microsoft Access function and not
supported by MS SQL Server.  If you're porting from MS Access, you'll
need to fix a lot more of your SQL than the functions.  There is,
however, help for MS Access users:
(for both of the above)
http://techdocs.postgresql.org/

> And is it possible to override the existing
> function and
> operator like "+" to become a concate?

How's your C porgramming?  It's an open-source database, you can do
anything for which you have the skill and the time.  The advisability of
doing so, however, is another thing entirely ...

Given that all of the MS SQL Server "peculiarities" are not ANSI SQL
standard, have you given any thought to porting by search-and-replace
script instead of trying to make Postgres support a bunch of
non-standard operators and functions?  It'd be easier.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

IsNull work like this

SELECT IsNull( user_name, '') FROM user ;

then the query will return empty string when the field user_name is null

Regards
Bill

> > Hello all,
> >
> >   I am a newbie to postgreSQL, is it possible to write a "DateDiff",
> > "IsNull" function work same as the one in SQL Server and are there and
> > sample out there? And is it possible to override the existing function
and
> > operator like "+" to become a concate?
>
> I don't know what IsNull does but:
>
> template1=> select coalesce(NULL, 'Hello');
>  case
> -------
>  Hello
> (1 row)
>
> I do know what DateDiff does:
>
> template1=> select '2001-08-14'::date - '6 weeks'::interval;
>         ?column?
> ------------------------
>  2001-07-03 00:00:00-05
> (1 row)
>
> Overloading operators? *Please* tell me that this is impossible.
>
> Alan Gutierrez

> Bill,
>
> >   I am a newbie to postgreSQL, is it possible to write a "DateDiff",
> > "IsNull" function work same as the one in SQL Server and are there
> > and
> > sample out there?
>
> I take it that you want to do this to port an exisiting application?
>
> If so, the DateDiff thing has already been covered in Roberto Mello's
> "cookbook".
>
> However, ISNULL, last I checked, was a Microsoft Access function and not
> supported by MS SQL Server.  If you're porting from MS Access, you'll
> need to fix a lot more of your SQL than the functions.  There is,
> however, help for MS Access users:
> (for both of the above)
> http://techdocs.postgresql.org/ I need to port a application that run on MS SQL Server/
Sybase SQL Anyway to postgreSQL, the 2 function list
about is the one used most in the application I don't know when MS SQL Server being to have the funtion
IsNull, but it exist from ver. 6.5 and up, and the syntax is
IsNull( field_name, default_value), and will return the
default_value when field is null

> > And is it possible to override the existing
> > function and
> > operator like "+" to become a concate?
>
> How's your C porgramming?  It's an open-source database, you can do
> anything for which you have the skill and the time.  The advisability of
> doing so, however, is another thing entirely ...
>
> Given that all of the MS SQL Server "peculiarities" are not ANSI SQL
> standard, have you given any thought to porting by search-and-replace
> script instead of trying to make Postgres support a bunch of
> non-standard operators and functions?  It'd be easier. Yup, I also think it is easier, but it is an order

Thanks for your help

Bill

"Bill" <bchie@opensys.com.hk> writes:
> IsNull work like this
> SELECT IsNull( user_name, '') FROM user ;
> then the query will return empty string when the field user_name is null

Apparently, IsNull is Microsoft-speak for the SQL standard's COALESCE
function.  COALESCE is defined as returning the first of its parameters
that is not NULL.
        regards, tom lane

Tom, Manuel, Ross

Not only do I learn that PostgreSQL supports operator overloading, I learn
that this is not a bad thing. It's a big day for me!

I tend to think of operator overloading as an over used, non-portable C++
feature. I could be wrong about C++ too. Probably am. Don't want to debate C++
here!  Its just that C++ is what made my knee jerk.

Thanks for showing me the value of operator overloading in PostgreSQL.

Alan

On Tue, 14 Aug 2001, Ross J. Reedstrom wrote:

> On Tue, Aug 14, 2001 at 06:51:33AM +0000, Alan Gutierrez wrote:
> >
> > Overloading operators? *Please* tell me that this is impossible.
>
> Well, Alan, overloading operators is sort of important to the user
> definable types in postgresql. And any cross-type functionality, actually.

Alan, Tom, Ross, etc:

> > Well, Alan, overloading operators is sort of important to the user
> > definable types in postgresql. And any cross-type functionality,
> actually.

Hmmm ... I wasn't aware that what SQL does is "operator overloading",
per se.

Instead, I was under the SQL-spec impression that operators were defined
within the context of their relative datatypes, and only within that
context.

For example, currently 730::INT / 7::INT works fine, but '2
years'::INTERVAL / '1 week'::INTERVAL gives me an "operator not defined"
error.  This is because nobody has had time to define the operator "/"
in the context of INTERVAL / INTERVAL.  When someone does (oh please?
grovel, grovel) it will be defined, not overloaded.

Similarly, the operator "+" has no standard defintion in the context of
VARCHAR + VARCHAR.  So how is defining it as a concatination operator
(whatever other problems there might be with that) "overloading"?

Or am I missing the point?

-Josh Berkus









______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Thanks all for the help :)
 I got to do all this, because there a existing system
running on SQL Server/SQL Anyway which using
all these syntax though out the coding, and I got to
make it support postgreSQL
 And here is a question relate to the DataDiff function
in SQL Server, is it possible to write a function that pass
the parameter like the DataDiff function, I mean:

DataDiff( Year, date1, date2)
but not
DataDiff( 'Year', date1, date2)

Is it possible to doing this? Define a enum data type?

Thanks
Bill