Skip site navigation (1) Skip section navigation (2)

Re: 8.3 PLpgSQL Can't Compare Records?

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 8.3 PLpgSQL Can't Compare Records?
Date: 2009-07-01 18:47:40
Message-ID: b42b73150907011147y1cf92109k6590bd7c9f4cd241@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2009-07-01 18:49:12
Subject: Re: 8.5 development schedule
Previous:From: Merlin MoncureDate: 2009-07-01 18:45:04
Subject: Re: 8.3 PLpgSQL Can't Compare Records?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group