Thread: 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
Attachment
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
Attachment
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