-- Add Compatibility operators
--
-- SQL below solves a particular case where in the search option in the list module (and possible other modules)
-- integers types are compared against text types. While this is perfectly 'valid' in MySQL, this is not valid in PostgreSQL.
--
-- An example of such SQL generated by TYPO3 is (see "uid" LIKE '%Old%'):
-- SELECT count(*) 			
-- FROM "tx_rvtcouponfeeds_programnames" 			
-- WHERE "pid" = 100 AND ("uid" LIKE '%Old%' OR "programname" LIKE '%Old%' OR "programurl" LIKE '%Old%')
-- 
-- The functions add compatibility operators for PostgreSQL to make sure comparison is possible and the SQL doesn't return an error.
--
-- Note: You may consider having a look at project mysqlcompat on http://pgfoundry.org/projects/mysqlcompat
--       and report in DBAL bugtracker if you need another compatibility operator added.
--
-- $Id: postgresql-compatibility.sql 29977 2010-02-13 13:18:32Z xperseguers $
-- R. van Twisk <typo3@rvt.dds.nl>


CREATE OR REPLACE FUNCTION t3compat_operator_like(t text, i integer) RETURNS boolean AS
$BODY$
BEGIN
	RETURN t LIKE i;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

CREATE OR REPLACE FUNCTION t3compat_operator_like(i integer, t text) RETURNS boolean AS
$BODY$
BEGIN
	RETURN i::text LIKE t;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

CREATE OR REPLACE FUNCTION t3compat_operator_eq(t text, i integer) RETURNS boolean AS
$BODY$
BEGIN
	RETURN i::text=t;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

CREATE OR REPLACE FUNCTION t3compat_operator_eq(i integer, t text) RETURNS boolean AS
$BODY$
BEGIN
	RETURN i::text=t;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

-- Operator for LIKE
CREATE OPERATOR ~~ (PROCEDURE = t3compat_operator_like, LEFTARG = integer, RIGHTARG = text);
CREATE OPERATOR ~~ (PROCEDURE = t3compat_operator_like, LEFTARG = text, RIGHTARG = integer);
-- Operator for Equality
CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = integer, RIGHTARG = text);
CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = text, RIGHTARG = integer);

-- LOCATE()
CREATE OR REPLACE FUNCTION locate(text, text, integer)
RETURNS integer AS $$
SELECT POSITION($1 IN SUBSTRING ($2 FROM $3)) + $3 - 1
$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE OR REPLACE FUNCTION locate(text, text)
RETURNS integer AS $$
SELECT locate($1, $2, 1)
$$ IMMUTABLE STRICT LANGUAGE SQL;

-- IFNULL
CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT COALESCE($1, $2)
$$ IMMUTABLE STRICT LANGUAGE SQL;

-- Remove Compatibility operators
--
--DROP OPERATOR ~~ (integer,text);
--DROP OPERATOR ~~ (text,integer);
--DROP OPERATOR = (integer,text);
--DROP OPERATOR = (text,integer);
--DROP FUNCTION t3compat_operator_like(integer, text);
--DROP FUNCTION t3compat_operator_like(text, integer);
--DROP FUNCTION t3compat_operator_eq(integer, text);
--DROP FUNCTION t3compat_operator_eq(text, integer);
--DROP FUNCTION locate(text, text);
--DROP FUNCTION locate(text, text, integer);
--DROP FUNCTION ifnull(anyelement, anyelement);

