pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot assign ROWTYPE, RECORD variables in PL/pgSQL
4+ messages / 3 participants
[nested] [flat]
* Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
@ 2002-11-06 22:27 Josh Berkus <josh@agliodbs.com>
2002-11-07 21:56 ` Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2002-11-06 22:27 UTC (permalink / raw)
To: Rison, Stuart <srison@rvc.ac.uk>; pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
2002-11-06 22:27 Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Josh Berkus <josh@agliodbs.com>
@ 2002-11-07 21:56 ` Neil Conway <neilc@samurai.com>
2002-11-07 22:57 ` Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Josh Berkus <josh@agliodbs.com>
2002-11-08 03:11 ` Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL elein <elein@sbcglobal.net>
0 siblings, 2 replies; 4+ messages in thread
From: Neil Conway @ 2002-11-07 21:56 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Rison, Stuart <srison@rvc.ac.uk>; pgsql-bugs@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
2002-11-06 22:27 Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Josh Berkus <josh@agliodbs.com>
2002-11-07 21:56 ` Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Neil Conway <neilc@samurai.com>
@ 2002-11-07 22:57 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-11-07 22:57 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL
2002-11-06 22:27 Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Josh Berkus <josh@agliodbs.com>
2002-11-07 21:56 ` Re: Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Neil Conway <neilc@samurai.com>
@ 2002-11-08 03:11 ` elein <elein@sbcglobal.net>
1 sibling, 0 replies; 4+ messages in thread
From: elein @ 2002-11-08 03:11 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; josh@agliodbs.com; +Cc: pgsql-bugs@postgresql.org; elein@norcov.com
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
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-11-08 03:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-06 22:27 Cannot assign ROWTYPE, RECORD variables in PL/pgSQL Josh Berkus <josh@agliodbs.com>
2002-11-07 21:56 ` Neil Conway <neilc@samurai.com>
2002-11-07 22:57 ` Josh Berkus <josh@agliodbs.com>
2002-11-08 03:11 ` elein <elein@sbcglobal.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox