From c10fe32124d7f1761bcec84d83b92e4aff37ba81 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 17 Jul 2004 21:01:07 +0000 Subject: [PATCH] Some bug fixes in the function mapping table in convert.c. Removed odbc.sql, because convert.c does its job. --- Makefile.am | 10 +-- convert.c | 32 ++++---- odbc.sgml | 20 +---- odbc.sql | 228 ---------------------------------------------------- 4 files changed, 21 insertions(+), 269 deletions(-) delete mode 100644 odbc.sql diff --git a/Makefile.am b/Makefile.am index 77facdb..6a0641b 100644 --- a/Makefile.am +++ b/Makefile.am @@ -2,7 +2,7 @@ # # Makefile.am for psqlodbc (PostgreSQL ODBC driver) # -# $Header: /home/heikki/psqlodbc-cvs-copy/psqlodbc/Makefile.am,v 1.13 2004/07/10 22:26:26 petere Exp $ +# $Header: /home/heikki/psqlodbc-cvs-copy/psqlodbc/Makefile.am,v 1.14 2004/07/17 21:01:07 petere Exp $ # #------------------------------------------------------------------------- @@ -34,17 +34,11 @@ endif EXTRA_psqlodbc_la_SOURCES = md5.c -dist_pkgdata_DATA = odbc.sql odbc-drop.sql -BUILT_SOURCES = odbc-drop.sql - EXTRA_DIST = README.unix license.txt notice.txt readme.txt odbcinst.ini \ libtool.m4 psqlodbc_win32.def win32.mak \ dlg_wingui.c win_setup.h win_md5.c setup.c \ psqlodbc.reg -MAINTAINERCLEANFILES = odbc-drop.sql \ +MAINTAINERCLEANFILES = \ Makefile.in config.guess config.h.in config.sub configure \ install-sh missing mkinstalldirs aclocal.m4 ltmain.sh \ libtool.m4 depcomp - -$(srcdir)/odbc-drop.sql: $(srcdir)/odbc.sql - sed -n '/^CREATE OR REPLACE FUNCTION/s/CREATE OR REPLACE FUNCTION \([^ (][^ (]*([^)]*)\).*/DROP FUNCTION \1;/p' $(srcdir)/odbc.sql >$(srcdir)/odbc-drop.sql diff --git a/convert.c b/convert.c index 6e0b00d..ef533ee 100644 --- a/convert.c +++ b/convert.c @@ -56,7 +56,7 @@ #endif /* - * How to map ODBC scalar functions {fn func(args)} to Postgres. + * How to map ODBC scalar functions {fn func(args)} to PostgreSQL. * This is just a simple substitution. List augmented from: * http://www.merant.com/datadirect/download/docs/odbc16/Odbcref/rappc.htm * - thomas 2000-04-03 @@ -68,15 +68,15 @@ char *mapFuncs[][2] = { /* { "DIFFERENCE", "difference" }, how to ? */ {"INSERT", "substring($1 from 1 for $2 - 1) || $4 || substring($1 from $2 + $3)" }, {"LCASE", "lower($*)" }, - {"LEFT", "ltrunc($*)" }, + {"LEFT", "substring($1 for $2)" }, {"%2LOCATE", "strpos($2, $1)" }, /* 2 parameters */ {"%3LOCATE", "strpos(substring($2 from $3), $1) + $3 - 1" }, /* 3 parameters */ - {"LENGTH", "char_length($*)"}, +/* { "LENGTH", "length" }, built_in */ /* { "LTRIM", "ltrim" }, built_in */ - {"RIGHT", "rtrunc($*)" }, - {"SPACE", "repeat('' '', $1)" }, + {"RIGHT", "substring($1 from char_length($1) - $2 + 1)" }, + {"SPACE", "repeat(' ', $1)" }, /* { "REPEAT", "repeat" }, built_in */ -/* { "REPLACE", "replace" }, ??? */ +/* { "REPLACE", "replace" }, built_in */ /* { "RTRIM", "rtrim" }, built_in */ /* { "SOUNDEX", "soundex" }, how to ? */ {"SUBSTRING", "substr($*)" }, @@ -86,7 +86,7 @@ char *mapFuncs[][2] = { /* { "ACOS", "acos" }, built_in */ /* { "ASIN", "asin" }, built_in */ /* { "ATAN", "atan" }, built_in */ -/* { "ATAN2", "atan2" }, bui;t_in */ +/* { "ATAN2", "atan2" }, built_in */ {"CEILING", "ceil($*)" }, /* { "COS", "cos" }, built_in */ /* { "COT", "cot" }, built_in */ @@ -109,12 +109,14 @@ char *mapFuncs[][2] = { {"TRUNCATE", "trunc($*)" }, {"CURRENT_DATE", "current_date" }, - {"CURRENT_TIME", "current_time" }, - {"CURRENT_TIMESTAMP", "current_timestamp" }, - {"LOCALTIME", "localtime" }, - {"LOCALTIMESTAMP", "localtimestamp" }, - {"CURRENT_USER", "cast(current_user as text)" }, - {"SESSION_USER", "cast(session_user as text)" }, + {"%0CURRENT_TIME", "current_time" }, + {"%1CURRENT_TIME", "current_time($1)" }, + {"%0CURRENT_TIMESTAMP", "current_timestamp" }, + {"%1CURRENT_TIMESTAMP", "current_timestamp($1)" }, + {"%0LOCALTIME", "localtime" }, + {"%1LOCALTIME", "localtime($1)" }, + {"%0LOCALTIMESTAMP", "localtimestamp" }, + {"%1LOCALTIMESTAMP", "localtimestamp($1)" }, {"CURDATE", "current_date" }, {"CURTIME", "current_time" }, {"DAYNAME", "to_char($1, 'Day')" }, @@ -131,9 +133,11 @@ char *mapFuncs[][2] = { {"WEEK", "cast(extract(week from $1) as integer)" }, {"YEAR", "cast(extract(year from $1) as integer)" }, -/* { "DATABASE", "database" }, */ + {"DATABASE", "current_database()" }, {"IFNULL", "coalesce($*)" }, {"USER", "cast(current_user as text)" }, + {"CURRENT_USER", "cast(current_user as text)" }, + {"SESSION_USER", "cast(session_user as text)" }, {0, 0} }; diff --git a/odbc.sgml b/odbc.sgml index 0cb003f..8f61c81 100644 --- a/odbc.sgml +++ b/odbc.sgml @@ -1,5 +1,5 @@ @@ -144,24 +144,6 @@ $Header: /home/heikki/psqlodbc-cvs-copy/psqlodbc/Attic/odbc.sgml,v 1.1 2002/10/2 useful, it should be arranged that the driver and the driver manager read the same configuration file. - - - odbc.sql - Additionally, you should install the ODBC catalog extensions. That will - provide a number of functions mandated by the ODBC standard that are not - supplied by PostgreSQL by default. The file - /usr/local/pgsql/share/odbc.sql (in the default installation layout) - contains the appropriate definitions, which you can install as follows: - -psql -d template1 -f LOCATION/odbc.sql - - where specifying template1 as the target - database will ensure that all subsequent new databases will have - these same definitions. If for any reason you want to remove - these functions again, run the file - odbc-drop.sql through - psql. - diff --git a/odbc.sql b/odbc.sql deleted file mode 100644 index 6d656e3..0000000 --- a/odbc.sql +++ /dev/null @@ -1,228 +0,0 @@ --- PostgreSQL catalog extensions for ODBC compatibility --- $Header: /home/heikki/psqlodbc-cvs-copy/psqlodbc/odbc.sql,v 1.7 2001/12/21 06:01:36 thomas Exp $ - --- ODBC functions are described here: --- - --- Note: If we format this file consistently we can automatically --- generate a corresponding "drop script". Start "CREATE" in the first --- column, and keep everything up to and including the argument list on --- the same line. See also the makefile rule. - - --- String Functions --- ++++++++++++++++ --- --- Built-in: ASCII, BIT_LENGTH, CHAR_LENGTH, CHARACTER_LENGTH, LTRIM, --- OCTET_LENGTH, POSITION, REPEAT, RTRIM, SUBSTRING --- Missing: DIFFERENCE, REPLACE, SOUNDEX, LENGTH (ODBC sense) --- Keyword problems: CHAR - - --- CHAR(code) -CREATE OR REPLACE FUNCTION "char"(integer) RETURNS text AS ' - SELECT chr($1); -' LANGUAGE SQL; - - --- CONCAT(string1, string2) -CREATE OR REPLACE FUNCTION concat(text, text) RETURNS text AS ' - SELECT $1 || $2; -' LANGUAGE SQL; - - --- INSERT(string1, start, len, string2) -CREATE OR REPLACE FUNCTION insert(text, integer, integer, text) RETURNS text AS ' - SELECT substring($1 from 1 for $2 - 1) || $4 || substring($1 from $2 + $3); -' LANGUAGE SQL; - - --- LCASE(string) -CREATE OR REPLACE FUNCTION lcase(text) RETURNS text AS ' - SELECT lower($1); -' LANGUAGE SQL; - - --- LEFT(string, count) -CREATE OR REPLACE FUNCTION left(text, integer) RETURNS text AS ' - SELECT substring($1 for $2); -' LANGUAGE SQL; - - --- LOCATE(substring, string[, start]) -CREATE OR REPLACE FUNCTION locate(text, text) RETURNS integer AS ' - SELECT position($1 in $2); -' LANGUAGE SQL; -CREATE OR REPLACE FUNCTION locate(text, text, integer) RETURNS integer AS ' - SELECT position($1 in substring($2 from $3)) + $3 - 1; -' LANGUAGE SQL; - - --- RIGHT(string, count) -CREATE OR REPLACE FUNCTION right(text, integer) RETURNS text AS ' - SELECT substring($1 from char_length($1) - $2 + 1); -' LANGUAGE SQL; - - --- SPACE(count) -CREATE OR REPLACE FUNCTION space(integer) RETURNS text AS ' - SELECT repeat('' '', $1); -' LANGUAGE SQL; - - --- UCASE(string) -CREATE OR REPLACE FUNCTION ucase(text) RETURNS text AS ' - SELECT upper($1); -' LANGUAGE SQL; - - --- Numeric Functions --- +++++++++++++++++ --- --- Built-in: ABS, ACOS, ASIN, ATAN, ATAN2, COS, COT, DEGRESS, EXP, --- FLOOR, MOD, PI, RADIANS, ROUND, SIGN, SIN, SQRT, TAN --- Missing: LOG (ODBC sense) - - --- CEILING(num) -CREATE OR REPLACE FUNCTION ceiling(numeric) RETURNS numeric AS ' - SELECT ceil($1); -' LANGUAGE SQL; - - --- LOG10(num) -CREATE OR REPLACE FUNCTION log10(double precision) RETURNS double precision AS ' - SELECT log($1); -' LANGUAGE SQL; -CREATE OR REPLACE FUNCTION log10(numeric) RETURNS numeric AS ' - SELECT log($1); -' LANGUAGE SQL; - - --- POWER(num, num) -CREATE OR REPLACE FUNCTION power(double precision, double precision) - RETURNS double precision AS ' - SELECT pow($1, $2); -' LANGUAGE SQL; -CREATE OR REPLACE FUNCTION power(numeric, numeric) - RETURNS numeric AS ' - SELECT pow($1, $2); -' LANGUAGE SQL; - - --- RAND([seed]) -CREATE OR REPLACE FUNCTION rand() RETURNS double precision AS ' - SELECT random(); -' LANGUAGE SQL; -CREATE OR REPLACE FUNCTION rand(double precision) RETURNS double precision AS ' - SELECT setseed($1); - SELECT random(); -' LANGUAGE SQL; - - --- TRUNCATE(num, places) -CREATE OR REPLACE FUNCTION truncate(numeric, integer) RETURNS numeric AS ' - SELECT trunc($1, $2); -' LANGUAGE SQL; - - --- Time, Date, and Interval Functions --- ++++++++++++++++++++++++++++++++++ --- --- Built-in: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, NOW --- Missing: none - - -CREATE OR REPLACE FUNCTION curdate() RETURNS date AS ' - SELECT current_date; -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION curtime() RETURNS time with time zone AS ' - SELECT current_time; -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION odbc_timestamp() RETURNS timestamp with time zone AS ' - SELECT current_timestamp; -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION dayname(timestamp) RETURNS text AS ' - SELECT to_char($1,''Day''); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION dayofmonth(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(day FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION dayofweek(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(dow FROM $1) AS integer) + 1; -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION dayofyear(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(doy FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION hour(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(hour FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION minute(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(minute FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION month(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(month FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION monthname(timestamp) RETURNS text AS ' - SELECT to_char($1, ''Month''); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION quarter(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(quarter FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION second(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(second FROM $1) AS integer); -' LANGUAGE SQL; - -/* --- The first argument is an integer constant denoting the units --- of the second argument. Until we know the actual values, we --- cannot implement these. - thomas 2000-04-11 -xCREATE OR REPLACE FUNCTION timestampadd(integer, integer, timestamp) - RETURNS timestamp AS ' - SELECT CAST(($3 + ($2 * $1)) AS timestamp); -' LANGUAGE SQL; - -xCREATE OR REPLACE FUNCTION timestampdiff(integer, integer, timestamp) - RETURNS timestamp AS ' - SELECT CAST(($3 + ($2 * $1)) AS timestamp); -' LANGUAGE SQL; -*/ - -CREATE OR REPLACE FUNCTION week(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(week FROM $1) AS integer); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION year(timestamp) RETURNS integer AS ' - SELECT CAST(EXTRACT(year FROM $1) AS integer); -' LANGUAGE SQL; - - --- System Functions --- ++++++++++++++++ --- --- Built-in: USER --- Missing: DATABASE, IFNULL - -CREATE OR REPLACE FUNCTION odbc_user() RETURNS text AS ' - SELECT CAST(current_user AS TEXT); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION odbc_current_user() RETURNS text AS ' - SELECT CAST(current_user AS TEXT); -' LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION odbc_session_user() RETURNS text AS ' - SELECT CAST(session_user AS TEXT); -' LANGUAGE SQL; -- 2.39.5