Dear list,
i'm trying to replicate tables containing XML-fields using Pg 8.4.4 and
9.0B4 with Bucardo and got:
DBD::Pg::st execute failed: ERROR: could not identify an equality
operator for type xml
So i provided a primitive equality operator for the XML type in schema
pg_catalog:
CREATE OR REPLACE FUNCTION pg_catalog.eq_xml_xml(XML,XML) RETURNS
BOOLEAN AS
'SELECT CAST($1 AS TEXT)=CAST($2 AS TEXT);' LANGUAGE SQL IMMUTABLE;
DROP OPERATOR IF EXISTS pg_catalog.= (XML,XML);
CREATE OPERATOR pg_catalog.=
(LEFTARG=XML,RIGHTARG=XML,PROCEDURE=pg_catalog.eq_xml_xml,COMMUTATOR= = );
COMMENT ON OPERATOR pg_catalog.= (XML,XML) IS 'equal';
This works nicely when comparing XML - fields in normal SQL, but
obviously has no effect when comparing records - the error persists.
I tracked the error message down to function record_eq() in rowtypes.c,
where the comment says:
* Lookup the equality function if not done already
Thats why i'm suspecting that user-defined operators are not heeded here.
Test case:
Include code from above, then:
CREATE TABLE test(id INTEGER PRIMARY KEY, attr XML);
INSERT INTO test(id,attr) VALUES (1,'<x>test</x>');
SELECT * FROM test WHERE id=1 AND attr=attr;
id | attr
----+-------------
1 | <x>test</x>
CREATE OR REPLACE FUNCTION test_record_eq() RETURNS BOOLEAN AS $$
DECLARE
a RECORD;
b RECORD;
BEGIN
SELECT * INTO a FROM test WHERE id=1;
SELECT * INTO b FROM test WHERE id=1;
RETURN (a = b);
END;
$$ LANGUAGE plpgsql STABLE;
SELECT test_record_eq();
ERROR: could not identify an equality operator for type xml
CONTEXT: PL/pgSQL function "test_record_eq" line 7 at RETURN
Just FYI: I'm using the XML-fields just as free-format data store, not
for complete XML-trees, e.g. '<age>20</age><name>Smith</name>'
And, yes, eq_xml_xml() will return false if XML field contents are
identical but just ordered differently, but that doesn't matter in my
case, as it just triggers an unnecessary sync.
Thanks for your help
wz