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
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 |