#!/bin/sh

#
# fix_ms_access.sh
# Author: Bill Chatfield <bill_chatfield@yahoo.com>
# This script is distributed under the LGPL.
#

#
# Script to fix the problem with using MS Access to link to a PostgreSQL
# table over ODBC.
#
# This is the error message:
#
# ERROR:  Unable to identify an operator '=' for types 'numeric' and 'float8'
#         You will have to retype this query using an explicit cast
#
# BUG! - The equals operator can't be removed with the drop command.  I don't
#        know why.  "drop operator = (numeric, float8);" fails.
#

#
# Check for command line parameter.
#
if [ $# -ne 1 ]
then
    echo usage: fix_ms_access.sh \<dbname\>
    exit 1
fi

DBNAME=$1

#
# Find out where the plpgsql language library is.
#
if [ "$PGLIB" = "" ]
then
    if [ -d /usr/lib/pgsql ]
    then
        # This is where it is in Red Hat Linux.
        PGLIB=/usr/lib/pgsql
    else
        echo Please set PGLIB to the directory where plpgsql.so is located.
        exit 1
    fi
fi

#
# Add the plpgsql procedural language to PostgreSQL.  The function is
# written in this language, so it is required.  plpgsql had to be used
# because the equivalent sql language script causes the backend to crash,
# tcl sucks, and PL/Perl is not included in Red Hat Linux.  Of course, C
# would require a native compiled shared library, which is not easy to
# move between architectures.
#
createlang plpgsql $DBNAME

#
# Add the equals operator for numeric and float8.
#
psql $DBNAME <<"EOF"

create function numeric_eq_float8(numeric, float8) returns boolean
as
'
    DECLARE
        numeric_var ALIAS FOR $1;
        float8_var ALIAS FOR $2;
    BEGIN
        -- Perform the equality check by casting the float8 to numeric.
        IF numeric_var = CAST(float8_var as numeric) THEN
            RETURN true;
        ELSE
            RETURN false;
        END IF;
    END;
'
language 'plpgsql'; 

create function numeric_ne_float8(numeric, float8) returns boolean
as
'
    DECLARE
        numeric_var ALIAS FOR $1;
        float8_var ALIAS FOR $2;
    BEGIN
        -- Perform the equality check by casting the float8 to numeric.
        IF numeric_var = CAST(float8_var as numeric) THEN
            RETURN false;
        ELSE
            RETURN true;
        END IF;
    END;
'
language 'plpgsql'; 

create operator =
( 
    leftarg=numeric, 
    rightarg=float8, 
    procedure=numeric_eq_float8, 
    commutator='=', 
    negator='<>'
);

create operator <>
( 
    leftarg=numeric, 
    rightarg=float8, 
    procedure=numeric_ne_float8, 
    commutator='<>', 
    negator='='
);

EOF