Search Postgresql Archives

cannot assign non-composite value to a row variable

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux