I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as Postgres doesn't have a built in operator for
varchar = uuid. Rather rewrite all my views or attempt a single massive migration, I wanted to temporarily create a uuid = varchar operator until the migration is completed.
I've never created a custom operator before and my attempt to below is not working:
CREATE OR REPLACE FUNCTION uuid_equal_varchar (varchar, uuid) RETURNS boolean AS 'SELECT $1::text = $2::text;' LANGUAGE sql IMMUTABLE; CREATE OPERATOR = ( leftarg = character varying, rightarg = uuid, procedure = uuid_equal_varchar, commutator = = );
However this operator breaks everything. Including a simple varchar = varchar comparison (see below):
SELECT * FROM test WHERE pk_test = '123'; ERROR: invalid input syntax for uuid: "123"
Can someone explain to me what I am doing wrong? Am I trying to attempt something that is not possible?