does record_eq() ignore user-defined operators?

Поиск
Список
Период
Сортировка
От Kurt
Тема does record_eq() ignore user-defined operators?
Дата
Msg-id 4C83555C.5000101@gmx.net
обсуждение исходный текст
Ответы Re: does record_eq() ignore user-defined operators?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: does record_eq() ignore user-defined operators?  (Kurt <wazkelzu@gmx.net>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: FC13 RPMs for 9.0 - on postgresql.org ftp, but not in yum repo?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: does record_eq() ignore user-defined operators?