Re: ODBC 3.0 functions (UCASE, LCASE, etc.)

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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington