Fixed bug #6196: IFNULL operator cannot be parsed
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / res / postgresql / postgresql-compatibility.sql
1 -- Add Compatibility operators
2 --
3 -- SQL below solves a particular case where in the search option in the list module (and possible other modules)
4 -- integers types are compared against text types. While this is perfectly 'valid' in MySQL, this is not valid in PostgreSQL.
5 --
6 -- An example of such SQL generated by TYPO3 is (see "uid" LIKE '%Old%'):
7 -- SELECT count(*)
8 -- FROM "tx_rvtcouponfeeds_programnames"
9 -- WHERE "pid" = 100 AND ("uid" LIKE '%Old%' OR "programname" LIKE '%Old%' OR "programurl" LIKE '%Old%')
10 --
11 -- The functions add compatibility operators for PostgreSQL to make sure comparison is possible and the SQL doesn't return an error.
12 --
13 -- Note: You may consider having a look at project mysqlcompat on http://pgfoundry.org/projects/mysqlcompat
14 -- and report in DBAL bugtracker if you need another compatibility operator added.
15 --
16 -- $Id$
17 -- R. van Twisk <typo3@rvt.dds.nl>
18
19
20 CREATE OR REPLACE FUNCTION t3compat_operator_like(t text, i integer) RETURNS boolean AS
21 $BODY$
22 BEGIN
23 RETURN t LIKE i;
24 END
25 $BODY$
26 LANGUAGE 'plpgsql' VOLATILE
27 COST 1;
28
29 CREATE OR REPLACE FUNCTION t3compat_operator_like(i integer, t text) RETURNS boolean AS
30 $BODY$
31 BEGIN
32 RETURN i::text LIKE t;
33 END
34 $BODY$
35 LANGUAGE 'plpgsql' VOLATILE
36 COST 1;
37
38 CREATE OR REPLACE FUNCTION t3compat_operator_eq(t text, i integer) RETURNS boolean AS
39 $BODY$
40 BEGIN
41 RETURN i::text=t;
42 END
43 $BODY$
44 LANGUAGE 'plpgsql' VOLATILE
45 COST 1;
46
47 CREATE OR REPLACE FUNCTION t3compat_operator_eq(i integer, t text) RETURNS boolean AS
48 $BODY$
49 BEGIN
50 RETURN i::text=t;
51 END
52 $BODY$
53 LANGUAGE 'plpgsql' VOLATILE
54 COST 1;
55
56 -- Operator for LIKE
57 CREATE OPERATOR ~~ (PROCEDURE = t3compat_operator_like, LEFTARG = integer, RIGHTARG = text);
58 CREATE OPERATOR ~~ (PROCEDURE = t3compat_operator_like, LEFTARG = text, RIGHTARG = integer);
59 -- Operator for Equality
60 CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = integer, RIGHTARG = text);
61 CREATE OPERATOR = (PROCEDURE = t3compat_operator_eq, LEFTARG = text, RIGHTARG = integer);
62
63 -- LOCATE()
64 CREATE OR REPLACE FUNCTION locate(text, text, integer)
65 RETURNS integer AS $$
66 SELECT POSITION($1 IN SUBSTRING ($2 FROM $3)) + $3 - 1
67 $$ IMMUTABLE STRICT LANGUAGE SQL;
68
69 CREATE OR REPLACE FUNCTION locate(text, text)
70 RETURNS integer AS $$
71 SELECT locate($1, $2, 1)
72 $$ IMMUTABLE STRICT LANGUAGE SQL;
73
74 -- IFNULL
75 CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
76 RETURNS anyelement AS $$
77 SELECT COALESCE($1, $2)
78 $$ IMMUTABLE STRICT LANGUAGE SQL;
79
80 -- Remove Compatibility operators
81 --
82 --DROP OPERATOR ~~ (integer,text);
83 --DROP OPERATOR ~~ (text,integer);
84 --DROP OPERATOR = (integer,text);
85 --DROP OPERATOR = (text,integer);
86 --DROP FUNCTION t3compat_operator_like(integer, text);
87 --DROP FUNCTION t3compat_operator_like(text, integer);
88 --DROP FUNCTION t3compat_operator_eq(integer, text);
89 --DROP FUNCTION t3compat_operator_eq(text, integer);
90 --DROP FUNCTION locate(text, text);
91 --DROP FUNCTION locate(text, text, integer);
92 --DROP FUNCTION ifnull(anyelement, anyelement);
93