Re: [HACKERS] 8.3 PLpgSQL Can't Compare Records?
David E. Wheeler wrote: This code: CREATE OR REPLACE FUNCTION foo() returns boolean as $$ DECLARE have_rec record; want_rec record; BEGIN have_rec := row(1, 2); want_rec := row(3, 5); RETURN have_rec IS DISTINCT FROM want_rec; END; $$ language plpgsql; SELECT ROW(1, 2) IS DISTINCT FROM ROW(3, 5); SELECT foo(); DROP FUNCTION foo(); Works as expected on 8.4, outputting: [...] On 8.3, however, the row comparisons in the SQL statement works, but fails in the PL/pgSQL function, with this output: ?column? -- t (1 row) psql:huh.sql:14: ERROR: operator does not exist: record = record LINE 1: SELECT $1 IS DISTINCT FROM $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT $1 IS DISTINCT FROM $2 CONTEXT: PL/pgSQL function foo line 7 at RETURN Is this a known issue in 8.3? If so, is there a known workaround? The change is probably here: http://archives.postgresql.org/pgsql-committers/2008-10/msg00110.php So I think it is safe to argue that this is not a bug in 8.3, but an improvement in 8.4. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 PLpgSQL Can't Compare Records?
On Jul 7, 2009, at 12:49 AM, Albe Laurenz wrote: Is this a known issue in 8.3? If so, is there a known workaround? The change is probably here: http://archives.postgresql.org/pgsql-committers/2008-10/msg00110.php So I think it is safe to argue that this is not a bug in 8.3, but an improvement in 8.4. Right, good find, thanks. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.3 PLpgSQL Can't Compare Records?
This code: CREATE OR REPLACE FUNCTION foo() returns boolean as $$ DECLARE have_rec record; want_rec record; BEGIN have_rec := row(1, 2); want_rec := row(3, 5); RETURN have_rec IS DISTINCT FROM want_rec; END; $$ language plpgsql; SELECT ROW(1, 2) IS DISTINCT FROM ROW(3, 5); SELECT foo(); DROP FUNCTION foo(); Works as expected on 8.4, outputting: ?column? -- t (1 row) Time: 48.626 ms foo - t (1 row) On 8.3, however, the row comparisons in the SQL statement works, but fails in the PL/pgSQL function, with this output: ?column? -- t (1 row) psql:huh.sql:14: ERROR: operator does not exist: record = record LINE 1: SELECT $1 IS DISTINCT FROM $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT $1 IS DISTINCT FROM $2 CONTEXT: PL/pgSQL function foo line 7 at RETURN Is this a known issue in 8.3? If so, is there a known workaround? Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 PLpgSQL Can't Compare Records?
On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheelerda...@kineticode.com wrote: This code: CREATE OR REPLACE FUNCTION foo() returns boolean as $$ DECLARE have_rec record; want_rec record; BEGIN have_rec := row(1, 2); want_rec := row(3, 5); RETURN have_rec IS DISTINCT FROM want_rec; END; $$ language plpgsql; SELECT ROW(1, 2) IS DISTINCT FROM ROW(3, 5); SELECT foo(); DROP FUNCTION foo(); Works as expected on 8.4, outputting: ?column? -- t (1 row) Time: 48.626 ms foo - t (1 row) On 8.3, however, the row comparisons in the SQL statement works, but fails in the PL/pgSQL function, with this output: ?column? -- t (1 row) psql:huh.sql:14: ERROR: operator does not exist: record = record LINE 1: SELECT $1 IS DISTINCT FROM $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT $1 IS DISTINCT FROM $2 CONTEXT: PL/pgSQL function foo line 7 at RETURN Is this a known issue in 8.3? If so, is there a known workaround? fyi: works in 8.4, as part of a broad fix of composite type comparison ops merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 PLpgSQL Can't Compare Records?
On Wed, Jul 1, 2009 at 2:45 PM, Merlin Moncuremmonc...@gmail.com wrote: On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheelerda...@kineticode.com wrote: This code: CREATE OR REPLACE FUNCTION foo() returns boolean as $$ DECLARE have_rec record; want_rec record; BEGIN have_rec := row(1, 2); want_rec := row(3, 5); RETURN have_rec IS DISTINCT FROM want_rec; END; $$ language plpgsql; SELECT ROW(1, 2) IS DISTINCT FROM ROW(3, 5); SELECT foo(); DROP FUNCTION foo(); Works as expected on 8.4, outputting: ?column? -- t (1 row) Time: 48.626 ms foo - t (1 row) On 8.3, however, the row comparisons in the SQL statement works, but fails in the PL/pgSQL function, with this output: ?column? -- t (1 row) psql:huh.sql:14: ERROR: operator does not exist: record = record LINE 1: SELECT $1 IS DISTINCT FROM $2 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT $1 IS DISTINCT FROM $2 CONTEXT: PL/pgSQL function foo line 7 at RETURN Is this a known issue in 8.3? If so, is there a known workaround? fyi: works in 8.4, as part of a broad fix of composite type comparison ops whoops, you knew that already :-). one possible workaround is: select $1::text is distinct from $2::text; merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 PLpgSQL Can't Compare Records?
On Jul 1, 2009, at 11:47 AM, Merlin Moncure wrote: fyi: works in 8.4, as part of a broad fix of composite type comparison ops whoops, you knew that already :-). one possible workaround is: select $1::text is distinct from $2::text Yes, and that's what I'm doing, although it is significantly less precise, in that: * Columns with different types may successfully compare (e.g., NULL and '') * No (easy) way to tell if two records have different numbers of columns But it's good enough for 8.3 if there is no other workaround. Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
