Thread: BUG #5154: ERROR: cannot assign non-composite value to a row variable

BUG #5154: ERROR: cannot assign non-composite value to a row variable

From

"Jeff Shanab"

Date:

31 October 2009, 01:17:15

The following bug has been logged online:

Bug reference:      5154
Logged by:          Jeff Shanab
Email address:      jshanab@earthlink.net
PostgreSQL version: 8.3.5
Operating system:   Linux
Description:        ERROR:  cannot assign non-composite value to a row
variable
Details:

As discussed on the irc. I had a problem with a utility function that was
being passed a NEW row and a null for the OLD row. The error was created
when it tries to store the row variable in the local variables. RhodiumToad
on the list provided this simple test.

create type foo1 as (a integer, b text);
CREATE TYPE
create type foo2 as (c integer, d foo1);
CREATE TYPE

create function foo() returns foo2 language plpgsql as $f$ declare v foo2;
begin v := null; return v; end; $f$;
CREATE FUNCTION

select foo();

ERROR: cannot assign non-composite value to a row variable

CONTEXT: PL/pgSQL function "foo" line 1 at assignment

Re: BUG #5154: ERROR: cannot assign non-composite value to a row variable

From

Pavel Stehule

Date:

31 October 2009, 04:42:00

Hello

2009/10/31 Jeff Shanab <jshanab@earthlink.net>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05154
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Jeff Shanab
> Email address: =C2=A0 =C2=A0 =C2=A0jshanab@earthlink.net
> PostgreSQL version: 8.3.5
> Operating system: =C2=A0 Linux
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0ERROR: =C2=A0cannot assign non-co=
mposite value to a row
> variable
> Details:
>
> As discussed on the irc. I had a problem with a utility function that was
> being passed a NEW row and a null for the OLD row. The error was created
> when it tries to store the row variable in the local variables. RhodiumTo=
ad
> on the list provided this simple test.
>
> create type foo1 as (a integer, b text);
> CREATE TYPE
> create type foo2 as (c integer, d foo1);
> CREATE TYPE
>
> create function foo() returns foo2 language plpgsql as $f$ declare v foo2;
> begin v :=3D null; return v; end; $f$;
> CREATE FUNCTION
>

This isn't bug - it is just feature. ROW value cannot contain scalar
value. If you would to like reset variable, use NULL row constant.

BEGIN
  v :=3D (NULL, NULL);

-- it is equalent to NULL

RAISE NOTICE 'IS IT NULL %', v IS NULL;

  RETURN NULL;
END;

Regards
Pavel Stehule

> select foo();
>
> ERROR: cannot assign non-composite value to a row variable
>
> CONTEXT: PL/pgSQL function "foo" line 1 at assignment
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Re: BUG #5154: ERROR: cannot assign non-composite value to a row variable

From

Andrew Gierth

Date:

01 November 2009, 02:18:35

>>>>> "Pavel" == Pavel Stehule <pavel.stehule@gmail.com> writes:

 >> As discussed on the irc. I had a problem with a utility function
 >> that was being passed a NEW row and a null for the OLD row. The
 >> error was created when it tries to store the row variable in the
 >> local variables. RhodiumToad on the list provided this simple
 >> test.
 >>
 >> create type foo1 as (a integer, b text);
 >> CREATE TYPE
 >> create type foo2 as (c integer, d foo1);
 >> CREATE TYPE
 >>
 >> create function foo() returns foo2 language plpgsql as $f$ declare v foo2;
 >> begin v := null; return v; end; $f$;
 >> CREATE FUNCTION

 Pavel> This isn't bug - it is just feature.

No, it's a bug.

Here's a clearer testcase:

create type foo1 as (a integer, b text);
create type foo2 as (c integer, d foo1);

create or replace function foo1() returns foo1 language plpgsql
 as $f$ declare v foo1; begin v := null::foo1; return v; end; $f$;

create or replace function foo2() returns foo2 language plpgsql
 as $f$ declare v foo2; begin v := null::foo2; return v; end; $f$;

select foo1();
 foo1
------
 (,)
(1 row)

select foo2();
ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/pgSQL function "foo2" line 1 at assignment

Alternatively:

create or replace function foo1(r foo1) returns foo1 language plpgsql
  as $f$ declare v foo1; begin v := r; return v; end; $f$;
create or replace function foo2(r foo2) returns foo2 language plpgsql
  as $f$ declare v foo2; begin v := r; return v; end; $f$;

select foo1(null);
 foo1
------
 (,)
(1 row)

select foo2(null);
ERROR:  cannot assign non-composite value to a row variable
CONTEXT:  PL/pgSQL function "foo2" while storing call arguments into local variables

These calls should either both work or both fail.

--
Andrew (irc:RhodiumToad)

Re: BUG #5154: ERROR: cannot assign non-composite value to a row variable

From

Pavel Stehule

Date:

01 November 2009, 05:54:55

2009/11/1 Andrew Gierth <andrew@tao11.riddles.org.uk>:
>>>>>> "Pavel" =3D=3D Pavel Stehule <pavel.stehule@gmail.com> writes:
>
> =C2=A0>> As discussed on the irc. I had a problem with a utility function
> =C2=A0>> that was being passed a NEW row and a null for the OLD row. The
> =C2=A0>> error was created when it tries to store the row variable in the
> =C2=A0>> local variables. RhodiumToad on the list provided this simple
> =C2=A0>> test.
> =C2=A0>>
> =C2=A0>> create type foo1 as (a integer, b text);
> =C2=A0>> CREATE TYPE
> =C2=A0>> create type foo2 as (c integer, d foo1);
> =C2=A0>> CREATE TYPE
> =C2=A0>>
> =C2=A0>> create function foo() returns foo2 language plpgsql as $f$ decla=
re v foo2;
> =C2=A0>> begin v :=3D null; return v; end; $f$;
> =C2=A0>> CREATE FUNCTION
>
> =C2=A0Pavel> This isn't bug - it is just feature.
>
> No, it's a bug.
>
> Here's a clearer testcase:
>
> create type foo1 as (a integer, b text);
> create type foo2 as (c integer, d foo1);
>
> create or replace function foo1() returns foo1 language plpgsql
> =C2=A0as $f$ declare v foo1; begin v :=3D null::foo1; return v; end; $f$;
>
> create or replace function foo2() returns foo2 language plpgsql
> =C2=A0as $f$ declare v foo2; begin v :=3D null::foo2; return v; end; $f$;
>
> select foo1();
> =C2=A0foo1
> ------
> =C2=A0(,)
> (1 row)
>
> select foo2();
> ERROR: =C2=A0cannot assign non-composite value to a row variable
> CONTEXT: =C2=A0PL/pgSQL function "foo2" line 1 at assignment
>
> Alternatively:
>
> create or replace function foo1(r foo1) returns foo1 language plpgsql
> =C2=A0as $f$ declare v foo1; begin v :=3D r; return v; end; $f$;
> create or replace function foo2(r foo2) returns foo2 language plpgsql
> =C2=A0as $f$ declare v foo2; begin v :=3D r; return v; end; $f$;
>
> select foo1(null);
> =C2=A0foo1
> ------
> =C2=A0(,)
> (1 row)
>
> select foo2(null);
> ERROR: =C2=A0cannot assign non-composite value to a row variable
> CONTEXT: =C2=A0PL/pgSQL function "foo2" while storing call arguments into=
 local variables
>
> These calls should either both work or both fail.
>

ok - it is bug. PL/pgSQL doesn't assign values well over nested
composite types. I am not sure, maybe this behave isn't limited by
NULL value.

Regards
Pavel Stehule


> --
> Andrew (irc:RhodiumToad)
>