Thread: ODBC 3.0 functions (UCASE, LCASE, etc.)
There are number of functions defined by ODBC 3 that we support,
but not using the exact same name or order of arguments as ODBC 3.0.
This means that some people may not choose PostgreSQL b/c it looks less
conformant. It also means that on over-simplifying tools like MySQL's
crash-me, we end up looking bad w/ rows of not-supported.
The arguments for including these seems clear.
The arguments against including these would be:
. users may have already defined their own functions w/these names (some
of them are pretty common, like MONTH(), UCASE(), etc.)
. we'd rather not do this, because we're adhering to some dreadful
standard, etc.
The first seems like a reasonable argument. Other than 'don't include by
default, but include an *.sql file w/ the distribution', are there other
ways of getting this into the server while minimizing impact on existing
apps?
(I know there was some discussion of creating portability layers, but that
seemed further off than the perl-script-that-converts-a-dump. Could
something come out of these?)
I'd be happy to spearhead a project to go through and find us some
existing wins. I think that the 5 seconds it takes to
CREATE FUNCTION LCASE(text) RETURNS TEXT AS 'begin return
lower($1); end;' LANGUAGE 'plpgsql' WITH (iscachable);
might pick us up more than a few points in the standardization/user
department.
Anyone interested?
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
This sounds good. Would these exist in ODBC or in the backend? My understanding is that these are best done in ODBC. > > There are number of functions defined by ODBC 3 that we support, > but not using the exact same name or order of arguments as ODBC 3.0. > > This means that some people may not choose PostgreSQL b/c it looks less > conformant. It also means that on over-simplifying tools like MySQL's > crash-me, we end up looking bad w/ rows of not-supported. > > The arguments for including these seems clear. > > The arguments against including these would be: > > . users may have already defined their own functions w/these names (some > of them are pretty common, like MONTH(), UCASE(), etc.) > > . we'd rather not do this, because we're adhering to some dreadful > standard, etc. > > The first seems like a reasonable argument. Other than 'don't include by > default, but include an *.sql file w/ the distribution', are there other > ways of getting this into the server while minimizing impact on existing > apps? > > (I know there was some discussion of creating portability layers, but that > seemed further off than the perl-script-that-converts-a-dump. Could > something come out of these?) > > > I'd be happy to spearhead a project to go through and find us some > existing wins. I think that the 5 seconds it takes to > > CREATE FUNCTION LCASE(text) RETURNS TEXT AS 'begin return > lower($1); end;' LANGUAGE 'plpgsql' WITH (iscachable); > > might pick us up more than a few points in the standardization/user > department. > > Anyone interested? > > -- > Joel Burton <jburton@scw.org> > Director of Information Systems, Support Center of Washington > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Thu, 3 May 2001, Bruce Momjian wrote: > > This sounds good. Would these exist in ODBC or in the backend? My > understanding is that these are best done in ODBC. It's not so much an ODBC problem *per se*, but, rather, that many databases offer 'functions' (some from standards, some made up) that we don't have. (The ODBC specs just happen to recommend a large slew of them.) I'd think these would have to be backend functions, but probably best not actually in the backend source. Since we can create functions easily, and since few of these things are actually new features, but re-named/re-ordered functions we already have, it wouldn't seem too onerous to make wrappers around these, or hook some of these in as aliases for existing functions/types. Things like: - aliasing int to mediumint - alias textcat to concat - iif( condition, true_expr, false_expr ) - first(), last() aggregates - std() as an alias for stddev() Yes, some of these *are* ugly, non-standard forms used by other databases. Read on for why I still think it could be a good idea. Some things we have (like existing odbc.sql file in src/interfaces/odbc), or in contrib/ (like soundex) are probably missed by many users. (And, although this is probably intentional, are left off of MySQL's crash-me). Perhaps a useful form would be a folder of function wrappers, group by 'competitor': oracle.sql mysql.sql access.sql odbc.sql which would contain the wrappers functions for these systems. Of course, we can't mimic *everything* (at least not in plpgsql functions ;-) ) but we might be able to do much better. I know it seems like a trivial thing, but it's not too infrequent that I'll hear someone chatting online/posting a follow-up message about how they've evaluated PostgreSQl, or another product, but didn't use it because we lacked feature foo(), when it's there, and just called feature bar(). Anyway, this isn't an itch that I *need* to scratch -- right now, all I use in the office is PostgreSQL for backend work :-). But I think in the 'how easy are we to evaluate and use' department, it could be a small, but helpful win. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
You might want to send this to -hackers and/or -odbc. Joel Burton writes: > There are number of functions defined by ODBC 3 that we support, > but not using the exact same name or order of arguments as ODBC 3.0. It's hard to tell how to proceed without a list of the proposed functions. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
OK, I am quoting at the top because I don't want to delete any of this. I think this is a great idea. In final form, it would be good to have a layer pre-parse the query string and rewrite Oracle-isms or MySQL-isms before they get to the parser, but as a first step, have a plug-in that would add this functionality is great. You could have it as an sql file that can be loaded right into a database. We already added Oracle functions for things we already didn't have. As these are synomyms, they would be best as loadable modules so they were not all in there at once. > > > > This sounds good. Would these exist in ODBC or in the backend? My > > understanding is that these are best done in ODBC. > > It's not so much an ODBC problem *per se*, but, rather, that many > databases offer 'functions' (some from standards, some made up) that we > don't have. (The ODBC specs just happen to recommend a large slew of > them.) > > I'd think these would have to be backend functions, but probably best not > actually in the backend source. > > Since we can create functions easily, and since few of these things are > actually new features, but re-named/re-ordered functions we already have, > it wouldn't seem too onerous to make wrappers around these, or hook some > of these in as aliases for existing functions/types. > > Things like: > > - aliasing int to mediumint > - alias textcat to concat > - iif( condition, true_expr, false_expr ) > - first(), last() aggregates > - std() as an alias for stddev() > > Yes, some of these *are* ugly, non-standard forms used by other databases. > Read on for why I still think it could be a good idea. > > Some things we have (like existing odbc.sql file in src/interfaces/odbc), > or in contrib/ (like soundex) are probably missed by many users. (And, > although this is probably intentional, are left off of MySQL's crash-me). > > Perhaps a useful form would be a folder of function wrappers, group by > 'competitor': > > oracle.sql > mysql.sql > access.sql > odbc.sql > > which would contain the wrappers functions for these systems. Of course, > we can't mimic *everything* (at least not in plpgsql functions ;-) ) > but we might be able to do much better. > > > I know it seems like a trivial thing, but it's not too infrequent that > I'll hear someone chatting online/posting a follow-up message about how > they've evaluated PostgreSQl, or another product, but didn't use it > because we lacked feature foo(), when it's there, and just called feature > bar(). > > Anyway, this isn't an itch that I *need* to scratch -- right now, > all I use in the office is PostgreSQL for backend work :-). But I think in > the 'how easy are we to evaluate and use' department, it could be a small, > but helpful win. > > -- > Joel Burton <jburton@scw.org> > Director of Information Systems, Support Center of Washington > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
This is clearly a good idea. It is easy to create SQL functions as synonyms for existing functions. You can even alter the arguments. I would encourage anyone who wants to start coding an SQL file of compatibility functions. > On Thu, 3 May 2001, Bruce Momjian wrote: > > > > > This sounds good. Would these exist in ODBC or in the backend? My > > understanding is that these are best done in ODBC. > > It's not so much an ODBC problem *per se*, but, rather, that many > databases offer 'functions' (some from standards, some made up) that we > don't have. (The ODBC specs just happen to recommend a large slew of > them.) > > I'd think these would have to be backend functions, but probably best not > actually in the backend source. > > Since we can create functions easily, and since few of these things are > actually new features, but re-named/re-ordered functions we already have, > it wouldn't seem too onerous to make wrappers around these, or hook some > of these in as aliases for existing functions/types. > > Things like: > > - aliasing int to mediumint > - alias textcat to concat > - iif( condition, true_expr, false_expr ) > - first(), last() aggregates > - std() as an alias for stddev() > > Yes, some of these *are* ugly, non-standard forms used by other databases. > Read on for why I still think it could be a good idea. > > Some things we have (like existing odbc.sql file in src/interfaces/odbc), > or in contrib/ (like soundex) are probably missed by many users. (And, > although this is probably intentional, are left off of MySQL's crash-me). > > Perhaps a useful form would be a folder of function wrappers, group by > 'competitor': > > oracle.sql > mysql.sql > access.sql > odbc.sql > > which would contain the wrappers functions for these systems. Of course, > we can't mimic *everything* (at least not in plpgsql functions ;-) ) > but we might be able to do much better. > > > I know it seems like a trivial thing, but it's not too infrequent that > I'll hear someone chatting online/posting a follow-up message about how > they've evaluated PostgreSQl, or another product, but didn't use it > because we lacked feature foo(), when it's there, and just called feature > bar(). > > Anyway, this isn't an itch that I *need* to scratch -- right now, > all I use in the office is PostgreSQL for backend work :-). But I think in > the 'how easy are we to evaluate and use' department, it could be a small, > but helpful win. > > -- > Joel Burton <jburton@scw.org> > Director of Information Systems, Support Center of Washington > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian writes: > This is clearly a good idea. It is easy to create SQL functions as > synonyms for existing functions. You can even alter the arguments. I > would encourage anyone who wants to start coding an SQL file of > compatibility functions. src/interfaces/odbc/odbc.sql RTFM rules ;-) > > > On Thu, 3 May 2001, Bruce Momjian wrote: > > > > > > > > This sounds good. Would these exist in ODBC or in the backend? My > > > understanding is that these are best done in ODBC. > > > > It's not so much an ODBC problem *per se*, but, rather, that many > > databases offer 'functions' (some from standards, some made up) that we > > don't have. (The ODBC specs just happen to recommend a large slew of > > them.) > > > > I'd think these would have to be backend functions, but probably best not > > actually in the backend source. > > > > Since we can create functions easily, and since few of these things are > > actually new features, but re-named/re-ordered functions we already have, > > it wouldn't seem too onerous to make wrappers around these, or hook some > > of these in as aliases for existing functions/types. > > > > Things like: > > > > - aliasing int to mediumint > > - alias textcat to concat > > - iif( condition, true_expr, false_expr ) > > - first(), last() aggregates > > - std() as an alias for stddev() > > > > Yes, some of these *are* ugly, non-standard forms used by other databases. > > Read on for why I still think it could be a good idea. > > > > Some things we have (like existing odbc.sql file in src/interfaces/odbc), > > or in contrib/ (like soundex) are probably missed by many users. (And, > > although this is probably intentional, are left off of MySQL's crash-me). > > > > Perhaps a useful form would be a folder of function wrappers, group by > > 'competitor': > > > > oracle.sql > > mysql.sql > > access.sql > > odbc.sql > > > > which would contain the wrappers functions for these systems. Of course, > > we can't mimic *everything* (at least not in plpgsql functions ;-) ) > > but we might be able to do much better. > > > > > > I know it seems like a trivial thing, but it's not too infrequent that > > I'll hear someone chatting online/posting a follow-up message about how > > they've evaluated PostgreSQl, or another product, but didn't use it > > because we lacked feature foo(), when it's there, and just called feature > > bar(). > > > > Anyway, this isn't an itch that I *need* to scratch -- right now, > > all I use in the office is PostgreSQL for backend work :-). But I think in > > the 'how easy are we to evaluate and use' department, it could be a small, > > but helpful win. > > > > -- > > Joel Burton <jburton@scw.org> > > Director of Information Systems, Support Center of Washington > > > > > > -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Bruce Momjian writes: > > > This is clearly a good idea. It is easy to create SQL functions as > > synonyms for existing functions. You can even alter the arguments. I > > would encourage anyone who wants to start coding an SQL file of > > compatibility functions. > > src/interfaces/odbc/odbc.sql > > RTFM rules ;-) Oh, good. I was thinking more of something for Sybase/Informix/ODBC in one place but we haven't had much demand for it lately except ODBC and we have Oracle in the backend, mostly. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026