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