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

cannot assign non-composite value to a row variable

From

Eliot Gable

Date:

04 June 2010, 13:43:15

In order to avoid using a 'FOR ... LOOP array_append(); END LOOP;' method of building an array (which is not at all efficient), I rewrote some of my code to do things more effectively. One of the steps involves building two arrays that are input to another stored procedure, but I am getting an error with this step. Specifically, I have something like this:

create type complex1 as ( ... ); -- one integer member and about 16 text members

create type complex2 as ( ... ); -- few integer members, about 10 text members, and about 6 different enum members

CREATE OR REPLACE blah ...

...

DECLARE

myvariable complex1[];

mydatasource complex1;

myrowsource complex2[];

...

BEGIN

...

-- The first way I tried to do it:

myvariable := array(

SELECT mydatasource FROM unnest(myrowsource)

);

-- The second way I tried to do it:

myvariable := array(

SELECT (mydatasource)::complex1 FROM unnest(myrowsource)

);

-- The third way I tried to do it:

myvariable := array(

SELECT (mydatasource.member1, mydatasource.member2, ...)::complex1 FROM unnest(myrowsource)

);

...

END ...

Each of these gives the same error message:

CONTEXT: ERROR

CODE: 42804

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

This is pl/pgsql in 8.4.1. Does anybody have any insight on how I can get around this issue? I'm not sure exactly what circumstances are involved in this SELECT that is causing this error. I don't understand what is being considered the row variable or what is being considered the non-composite value. I get the error when the 'myrowsource' variable has no rows, as well as when it has 2 rows.

Basically, all I want is to have myvariable be an array that has one 'row' for each row in 'unnest(myrowsource)' with the value of each row being equal to the 'mydatasource' contents. Maybe there is a better way to achieve that which someone can point out?

Thanks for any assistance anyone can provide.

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero

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

From

Alban Hertroys

Date:

04 June 2010, 14:08:04

On 4 Jun 2010, at 15:37, Eliot Gable wrote:

> CREATE OR REPLACE blah ...
> ...
> DECLARE
>   myvariable complex1[];
>   mydatasource complex1;
>   myrowsource complex2[];
> ...
> BEGIN
> ...
>   -- The first way I tried to do it:
>   myvariable := array(
>     SELECT mydatasource FROM unnest(myrowsource)
>   );

I don't see what you're trying to do here; apparently myrowsource has a column named mydatasource, but how is PG
supposedto know which mydatasource you mean - the declared one or the one from mydatasource? The same goes for
myrowsource.
I'm pretty sure you have a naming conflict.

Or did you intend to write:
    myvariable := array(unnest(myrowsource));

That probably still doesn't work. I have no installation of 8.4 at my disposal atm, so I can't verify, but IIRC unnest
doesn'treturn an array, but something else. You probably need to cast it to the right type first. 

> Each of these gives the same error message:
>
> CONTEXT: ERROR
> CODE: 42804
> MESSAGE: cannot assign non-composite value to a row variable

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4c0908b610152006515388!

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

From

Tom Lane

Date:

04 June 2010, 15:23:45

Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
> I'm pretty sure you have a naming conflict.

Yeah.  Specifically, the given example looks like it would try to assign
a null to the target variable, since it'd be taking the null value of a
different variable instead of a value from the intended source.

I believe the bizarre error message is coming from a plpgsql bug that we
fixed in 8.4.3, which basically was that assigning a null to a composite
variable would fail in some cases.  If you weren't shooting yourself in
the foot with naming conflicts, you might not trip over that case ...
but an update to 8.4.recent wouldn't be a bad idea anyway.

            regards, tom lane

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

From

Eliot Gable

Date:

04 June 2010, 17:34:37

Thanks for the note on the bugfix in the update. I will try it. However, there is no naming conflict. The idea was this:

The select query should return one result row for each row in the FROM clause since there is no WHERE clause. Each result row should be the contents of the complex1 data type contained by mydatasource. That set of resulting rows should be converted to an array and assigned to myvariable.

On Fri, Jun 4, 2010 at 11:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
> I'm pretty sure you have a naming conflict.

Yeah. Specifically, the given example looks like it would try to assign
a null to the target variable, since it'd be taking the null value of a
different variable instead of a value from the intended source.

I believe the bizarre error message is coming from a plpgsql bug that we
fixed in 8.4.3, which basically was that assigning a null to a composite
variable would fail in some cases. If you weren't shooting yourself in
the foot with naming conflicts, you might not trip over that case ...
but an update to 8.4.recent wouldn't be a bad idea anyway.

regards, tom lane

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero

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

From

Tom Lane

Date:

04 June 2010, 17:40:55

Eliot Gable <egable+pgsql-general@gmail.com> writes:
> Thanks for the note on the bugfix in the update. I will try it. However,
> there is no naming conflict.

There was most certainly a naming conflict in the sample code you
posted.  I realize that that probably was not the code you were
actually trying to use, but we can only go on what you show us.

            regards, tom lane

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

From

Eliot Gable

Date:

04 June 2010, 20:13:38

This is the code I posted:

create type complex1 as ( ... ); -- one integer member and about 16 text members

create type complex2 as ( ... ); -- few integer members, about 10 text members, and about 6 different enum members

CREATE OR REPLACE blah ...

...

DECLARE

myvariable complex1[];

mydatasource complex1;

myrowsource complex2[];

...

BEGIN

...

-- The first way I tried to do it:

myvariable := array(

SELECT mydatasource FROM unnest(myrowsource)

);

-- The second way I tried to do it:

myvariable := array(

SELECT (mydatasource)::complex1 FROM unnest(myrowsource)

);

-- The third way I tried to do it:

myvariable := array(

SELECT (mydatasource.member1, mydatasource.member2, ...)::complex1 FROM unnest(myrowsource)

);

...

END ...

I think you are thinking there is a naming conflict because you are seeing this:

SELECT mydatasource FROM unnest(myrowsource)

And also seeing this:

DECLARE

myvariable complex1[];

mydatasource complex1;

myrowsource complex2[];

And also think that there is a column called mydatasource in unnest(myrowsource). But there is no such column. If you are thinking there is a naming conflict for another reason, please explain, because I'm not seeing it.

What I am doing here is rather strange, and maybe there is a better way to do it. I'm just not aware of it. I'm relatively new to PostgreSQL. As I said, what I'm expecting my code to do is literally:

1. unnest the myrowsource array into rows

2. use the current contents of the previously declared mydatasource variable, which is of type 'complex1' to

3. generate as many rows using those contents as there are rows in the unnesting of myrowsource array

4. construct an array based on the resulting set of rows and store it in myvariable

On Fri, Jun 4, 2010 at 1:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Eliot Gable <egable+pgsql-general@gmail.com> writes:
> Thanks for the note on the bugfix in the update. I will try it. However,
> there is no naming conflict.

There was most certainly a naming conflict in the sample code you
posted. I realize that that probably was not the code you were
actually trying to use, but we can only go on what you show us.

regards, tom lane

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

From

Alvaro Herrera

Date:

04 June 2010, 21:43:03

Excerpts from Eliot Gable's message of vie jun 04 16:13:28 -0400 2010:
> This is the code I posted:
>
> create type complex1 as ( ... ); -- one integer member and about 16 text
> members
> create type complex2 as ( ... ); -- few integer members, about 10 text
> members, and about 6 different enum members

If you present some sample code that somebody can paste directly into a
psql session, we can provide useful help.  Nobody here has time to
construct such things from vague descriptions.

--
Álvaro Herrera <alvherre@alvh.no-ip.org>

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

From

Eliot Gable

Date:

04 June 2010, 23:44:15

Updating did solve the problem. Thanks.

On Fri, Jun 4, 2010 at 11:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
> I'm pretty sure you have a naming conflict.

Yeah. Specifically, the given example looks like it would try to assign
a null to the target variable, since it'd be taking the null value of a
different variable instead of a value from the intended source.

I believe the bizarre error message is coming from a plpgsql bug that we
fixed in 8.4.3, which basically was that assigning a null to a composite
variable would fail in some cases. If you weren't shooting yourself in
the foot with naming conflicts, you might not trip over that case ...
but an update to 8.4.recent wouldn't be a bad idea anyway.

regards, tom lane