pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: DateDiff, IsNull?
11+ messages / 6 participants
[nested] [flat]
* Re: DateDiff, IsNull?
@ 2001-08-14 06:51 Alan Gutierrez <alangutierrez@hotmail.com>
parent: Bill <bchie@opensys.com.hk>
1 sibling, 4 replies; 11+ messages in thread
From: Alan Gutierrez @ 2001-08-14 06:51 UTC (permalink / raw)
To: Bill <bchie@opensys.com.hk>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* DateDiff, IsNull?
@ 2001-08-14 11:24 Bill <bchie@opensys.com.hk>
0 siblings, 2 replies; 11+ messages in thread
From: Bill @ 2001-08-14 11:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: DateDiff, IsNull?
@ 2001-08-14 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alan Gutierrez <alangutierrez@hotmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2001-08-14 14:49 UTC (permalink / raw)
To: Alan Gutierrez <alangutierrez@hotmail.com>; +Cc: Bill <bchie@opensys.com.hk>; pgsql-sql
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 an explicit 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DateDiff, IsNull?
@ 2001-08-14 15:16 Josh Berkus <josh@agliodbs.com>
parent: Bill <bchie@opensys.com.hk>
1 sibling, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2001-08-14 15:16 UTC (permalink / raw)
To: Bill <bchie@opensys.com.hk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: DateDiff, IsNull?
@ 2001-08-14 15:34 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Alan Gutierrez <alangutierrez@hotmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Ross J. Reedstrom @ 2001-08-14 15:34 UTC (permalink / raw)
To: Alan Gutierrez <alangutierrez@hotmail.com>; +Cc: Bill <bchie@opensys.com.hk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: DateDiff, IsNull?
@ 2001-08-14 16:00 Manuel Sugawara <masm@fciencias.unam.mx>
parent: Alan Gutierrez <alangutierrez@hotmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Manuel Sugawara @ 2001-08-14 16:00 UTC (permalink / raw)
To: Alan Gutierrez <alangutierrez@hotmail.com>; +Cc: Bill <bchie@opensys.com.hk>; pgsql-sql
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=#
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DateDiff, IsNull?
@ 2001-08-15 03:44 Bill <bchie@opensys.com.hk>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bill @ 2001-08-15 03:44 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DateDiff, IsNull?
@ 2001-08-15 03:44 Bill <bchie@opensys.com.hk>
parent: Alan Gutierrez <alangutierrez@hotmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Bill @ 2001-08-15 03:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: DateDiff, IsNull?
@ 2001-08-15 04:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bill <bchie@opensys.com.hk>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2001-08-15 04:35 UTC (permalink / raw)
To: Bill <bchie@opensys.com.hk>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: DateDiff, IsNull?
@ 2001-08-15 13:49 Alan Gutierrez <alangutierrez@hotmail.com>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 11+ messages in thread
From: Alan Gutierrez @ 2001-08-15 13:49 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Re: Re: DateDiff, IsNull?
@ 2001-08-15 15:10 Josh Berkus <josh@agliodbs.com>
parent: Alan Gutierrez <alangutierrez@hotmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2001-08-15 15:10 UTC (permalink / raw)
To: Alan Gutierrez <alangutierrez@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2001-08-15 15:10 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-14 11:24 DateDiff, IsNull? Bill <bchie@opensys.com.hk>
2001-08-14 06:51 ` Alan Gutierrez <alangutierrez@hotmail.com>
2001-08-14 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-14 15:34 ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-08-15 13:49 ` Alan Gutierrez <alangutierrez@hotmail.com>
2001-08-15 15:10 ` Josh Berkus <josh@agliodbs.com>
2001-08-14 16:00 ` Manuel Sugawara <masm@fciencias.unam.mx>
2001-08-15 03:44 ` Bill <bchie@opensys.com.hk>
2001-08-15 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-14 15:16 ` Josh Berkus <josh@agliodbs.com>
2001-08-15 03:44 ` Bill <bchie@opensys.com.hk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox