Thread: 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
On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheeler<david@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
On Wed, Jul 1, 2009 at 2:45 PM, Merlin Moncure<mmoncure@gmail.com> wrote: > On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheeler<david@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
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
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
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