Thread: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL

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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 :=3D this_row;

return that_row.first_name;

end;'
language 'plpgsql';
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

... it will error out at the assignment "that_row :=3D this_row".

For that matter, any attempt to assign the contents of two ROWTYPE or RECOR=
D=20
variables directly to each other will error out:

that_record :=3D 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=
=20
the source table.   While a relatively easy workaround, this behaviour is=
=20
annoying and inconsistent.  It would be nice to fix in 7.3.1 or 7.4.

Thanks for your attention.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco

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=20
wild, possibly with alignment and memory context.  For example=20
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=20
the UDT writer write their own deep copy function if their type=20
is not compatible with a straight memcpy.

One of the other reasons this was a real PITB at informix was that=20
columns could also contain row (composite) types.  We do not=20
have that feature (yet?), but if deep copy is done in a type=20
blind way which is open to adding recursion we would not shut=20
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=20
has been a while since I looked at the problem so maybe it is a=20
non-issue.  But I'm raising it just in case...

elein


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 :=3D this_row;
> >
> > return that_row.first_name;
> >
> > end;'
> > language 'plpgsql';
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >
> > ... it will error out at the assignment "that_row :=3D 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