cannot assign non-composite value to a row variable
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
On 4 Jun 2010, at 15:37, Eliot Gable wrote:
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)
);
I don't see what you're trying to do here; apparently myrowsource has a column named mydatasource, but how is PG supposed to know which mydatasource you mean - the declared one or the one from mydatasource? The same goes for myrowsource.
I'm pretty sure you have a naming conflict.
Or did you intend to write:
myvariable := array(unnest(myrowsource));
That probably still doesn't work. I have no installation of 8.4 at my disposal atm, so I can't verify, but IIRC unnest doesn't return an array, but something else. You probably need to cast it to the right type first.
Each of these gives the same error message:
CONTEXT: ERROR
CODE: 42804
MESSAGE: cannot assign non-composite value to a row variable
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c0908b610152006515388!
Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
I'm pretty sure you have a naming conflict.
Yeah. Specifically, the given example looks like it would try to assign
a null to the target variable, since it'd be taking the null value of a
different variable instead of a value from the intended source.
I believe the bizarre error message is coming from a plpgsql bug that we
fixed in 8.4.3, which basically was that assigning a null to a composite
variable would fail in some cases. If you weren't shooting yourself in
the foot with naming conflicts, you might not trip over that case ...
but an update to 8.4.recent wouldn't be a bad idea anyway.
regards, tom lane
Thanks for the note on the bugfix in the update. I will try it. However,
there is no naming conflict. The idea was this:
The select query should return one result row for each row in the FROM
clause since there is no WHERE clause. Each result row should be the
contents of the complex1 data type contained by mydatasource. That set of
resulting rows should be converted to an array and assigned to myvariable.
On Fri, Jun 4, 2010 at 11:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
I'm pretty sure you have a naming conflict.
Yeah. Specifically, the given example looks like it would try to assign
a null to the target variable, since it'd be taking the null value of a
different variable instead of a value from the intended source.I believe the bizarre error message is coming from a plpgsql bug that we
fixed in 8.4.3, which basically was that assigning a null to a composite
variable would fail in some cases. If you weren't shooting yourself in
the foot with naming conflicts, you might not trip over that case ...
but an update to 8.4.recent wouldn't be a bad idea anyway.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
Eliot Gable <egable+pgsql-general@gmail.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
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@sss.pgh.pa.us> wrote:
Eliot Gable <egable+pgsql-general@gmail.com<egable%2Bpgsql-general@gmail.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
Excerpts from Eliot Gable's message of vie jun 04 16:13:28 -0400 2010:
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
If you present some sample code that somebody can paste directly into a
psql session, we can provide useful help. Nobody here has time to
construct such things from vague descriptions.
--
Álvaro Herrera <alvherre@alvh.no-ip.org>
Updating did solve the problem. Thanks.
On Fri, Jun 4, 2010 at 11:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alban Hertroys <dalroi@solfertje.student.utwente.nl> writes:
I'm pretty sure you have a naming conflict.
Yeah. Specifically, the given example looks like it would try to assign
a null to the target variable, since it'd be taking the null value of a
different variable instead of a value from the intended source.I believe the bizarre error message is coming from a plpgsql bug that we
fixed in 8.4.3, which basically was that assigning a null to a composite
variable would fail in some cases. If you weren't shooting yourself in
the foot with naming conflicts, you might not trip over that case ...
but an update to 8.4.recent wouldn't be a bad idea anyway.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