Re: Copying a rowtype variable.
2) I am looking for an elegant way of copying a rowtype variable:
eg.
DECLARE
current_row orf%ROWTYPE;
previous_row orf%ROWTYPE;
BEGINLOOP
-- use cursors or FOR SELECT to get values into current_row
-- now try this:previous_row = current_row;
END LOOP;
END;Now, as I anticipated, this fails because a rowtype variable is a
composite
variable. One working alternative is to do:I haven't tried this. One thing I notice above is that you're using
the equality operator "=" instead of the assignment operator ":=" .
Usually Postgres lets you slack on this, but it would be worth trying
to see whether that has an effect on the problem.
Fair point. But "previous_row := current_row" doesn't work either.
Another thing to try is, instead of a simple variable assignment
SELECT current_row INTO previous_row;
... and see if that works.
Well, I had high hopes for that one... but it didn't work either!
I'll tinker later today; there has to be a way to do it.
I'd definitely appreciate further suggestions, but thanks all the same for
you help. I have a feeling that you might have to write a PL function to
perform the operation... but I haven't really thought about it!
Stuart.
Bug: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
Affects: PL/pgSQL
Severity: Annoyance
Priority: Minor Enhancement
Confirmed On: 7.3beta2, Linux
Given the following function:
=======================================
create or replace function rowtype_test ()
returns text as '
declare this_row candidates%rowtype;
that_row candidates%rowtype;
begin
select * into this_row
from candidates;
that_row := this_row;
return that_row.first_name;
end;'
language 'plpgsql';
=======================================
... it will error out at the assignment "that_row := this_row".
For that matter, any attempt to assign the contents of two ROWTYPE or RECORD
variables directly to each other will error out:
that_record := this_record;
SELECT this_row INTO that_row;
SELECT * INTO that_row
FROM this_row;
The only way to populate that_row with a copy of this_row is by re-querying
the source table. While a relatively easy workaround, this behaviour is
annoying and inconsistent. It would be nice to fix in 7.3.1 or 7.4.
Thanks for your attention.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
I would personnaly like this feature (assigning a composite from another
similar composite) to be added to PLPGSQL. Another nice feature would be
to able to insert a composite into a table without have to name all
atributes.
Just my $.02
"Rison, Stuart" wrote:
Show quoted text
2) I am looking for an elegant way of copying a rowtype variable:
eg.
DECLARE
current_row orf%ROWTYPE;
previous_row orf%ROWTYPE;
BEGINLOOP
-- use cursors or FOR SELECT to get values into current_row
-- now try this:previous_row = current_row;
END LOOP;
END;Now, as I anticipated, this fails because a rowtype variable is a
composite
variable. One working alternative is to do:I haven't tried this. One thing I notice above is that you're using
the equality operator "=" instead of the assignment operator ":=" .
Usually Postgres lets you slack on this, but it would be worth trying
to see whether that has an effect on the problem.Fair point. But "previous_row := current_row" doesn't work either.
Another thing to try is, instead of a simple variable assignment
SELECT current_row INTO previous_row;
... and see if that works.
Well, I had high hopes for that one... but it didn't work either!
I'll tinker later today; there has to be a way to do it.
I'd definitely appreciate further suggestions, but thanks all the same for
you help. I have a feeling that you might have to write a PL function to
perform the operation... but I haven't really thought about it!Stuart.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Josh Berkus <josh@agliodbs.com> writes:
create or replace function rowtype_test ()
returns text as '
declare this_row candidates%rowtype;
that_row candidates%rowtype;
begin
select * into this_row
from candidates;that_row := this_row;
return that_row.first_name;
end;'
language 'plpgsql';
=======================================... it will error out at the assignment "that_row := this_row".
So we'd want a deep copy, right?
The only way to populate that_row with a copy of this_row is by re-querying
the source table.
Well, you can also iterate through the fields of this_row and assign
them to that_row manually -- of course, that's not much better.
While a relatively easy workaround, this behaviour is annoying and
inconsistent. It would be nice to fix in 7.3.1 or 7.4.
Unless anyone sees a problem with this, I'll work on this. I
definately think it's inappropriate for 7.3.1 though.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil,
Unless anyone sees a problem with this, I'll work on this. I
definately think it's inappropriate for 7.3.1 though.
Thank you!
-Josh Berkus
Deep copy with User defined data types sometimes get a little
wild, possibly with alignment and memory context. For example
a UDT which is a char followed by an int might be tricky to recognize
that alignment might be needed. It might even be better to have
the UDT writer write their own deep copy function if their type
is not compatible with a straight memcpy.
One of the other reasons this was a real PITB at informix was that
columns could also contain row (composite) types. We do not
have that feature (yet?), but if deep copy is done in a type
blind way which is open to adding recursion we would not shut
the door on the possibility. Tables have rows which have columns
containing rows which have columns containing udts and rows....
However, I suspect that postgresql row handling is a lot cleaner than
the informix row handling (with or without rows as columns) and it
has been a while since I looked at the problem so maybe it is a
non-issue. But I'm raising it just in case...
elein
Show quoted text
On Thursday 07 November 2002 13:56, Neil Conway wrote:
Josh Berkus <josh@agliodbs.com> writes:
create or replace function rowtype_test ()
returns text as '
declare this_row candidates%rowtype;
that_row candidates%rowtype;
begin
select * into this_row
from candidates;that_row := this_row;
return that_row.first_name;
end;'
language 'plpgsql';
=======================================... it will error out at the assignment "that_row := this_row".
So we'd want a deep copy, right?
The only way to populate that_row with a copy of this_row is by
re-querying the source table.Well, you can also iterate through the fields of this_row and assign
them to that_row manually -- of course, that's not much better.While a relatively easy workaround, this behaviour is annoying and
inconsistent. It would be nice to fix in 7.3.1 or 7.4.Unless anyone sees a problem with this, I'll work on this. I
definately think it's inappropriate for 7.3.1 though.Cheers,
Neil