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(at)kineticode(dot)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(at)gmail(dot)com> wrote:
> On Wed, Jul 1, 2009 at 1:35 PM, David E. Wheeler<david(at)kineticode(dot)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