ODBC 3.0 functions (UCASE, LCASE, etc.)

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

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

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

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 ;-)

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

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