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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Eliot Gable <egable+pgsql-general(at)gmail(dot)com<egable%2Bpgsql-general(at)gmail(dot)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
>

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