cannot assign non-composite value to a row variable

From: Eliot Gable <egable+pgsql-general(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: cannot assign non-composite value to a row variable
Date: 2010-06-04 13:37:43
Message-ID: AANLkTinumA49ynju022Sremw5iOb6KAnZxzWYwmOTb7u@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2010-06-04 14:07:35 Re: cannot assign non-composite value to a row variable
Previous Message Bill Moran 2010-06-04 12:17:52 Re: please help me. I can't pg_dumg DB