FAQ
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

Search Discussions

  • Merlin Moncure at Jul 1, 2009 at 6:45 pm

    On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheelerwrote:
    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
  • Merlin Moncure at Jul 1, 2009 at 6:47 pm

    On Wed, Jul 1, 2009 at 2:45 PM, Merlin Moncurewrote:
    On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheelerwrote:
    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
  • David E. Wheeler at Jul 1, 2009 at 7:36 pm

    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
  • Albe Laurenz at Jul 7, 2009 at 7:51 am

    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
  • David E. Wheeler at Jul 7, 2009 at 4:03 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 1, '09 at 5:35p
activeJul 7, '09 at 4:03p
posts6
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase