pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCopying a rowtype variable.
5+ messages / 3 participants
[nested] [flat]
* Copying a rowtype variable.
@ 2002-11-05 13:33 Rison, Stuart <srison@rvc.ac.uk>
2002-11-05 16:55 ` Re: [SQL] Copying a rowtype variable. Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rison, Stuart @ 2002-11-05 13:33 UTC (permalink / raw)
To: 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org>; pgsql-sql
Hello,
Just doing a bit of PL/PGSQL so my first question is:
1) should PL/PGSQL questions be posted to the general mailing list, the sql
mailing list or both?
My second question is:
2) I am looking for an elegant way of copying a rowtype variable:
eg.
DECLARE
current_row orf%ROWTYPE;
previous_row orf%ROWTYPE;
BEGIN
LOOP
-- 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:
previous_row.pid = current_row.pid;
previous_row.start = current_row.start;
-- and so on so forth for all current_row variables
But this is inconvenient of the row has many fields and impossible if you
want a flexible function which can accomodate rowtypes for which the fields
are not known "a priori".
Any clever workarounds?
Cheers,
Stuart.
PS. I started using PGSQL five years ago... then I had to leave it alone
for a while... I can't believe how much it's grown and developed. AMAZING,
congrats to all developers.
----------------------------------------------------------------------------
--
Stuart C. G. Rison
Department of Pathology and Infectious Diseases
Royal Veterinary College
London.
United Kingdom.
"That's bioinformatics... and we don't want to be doing bioinformatics, we
want to do real science.", Sydney Brenner, UCL Pfizer Lecture, 8 May 2001
----------------------------------------------------------------------------
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Copying a rowtype variable.
2002-11-05 13:33 Copying a rowtype variable. Rison, Stuart <srison@rvc.ac.uk>
@ 2002-11-05 16:55 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-11-05 16:55 UTC (permalink / raw)
To: Rison, Stuart <srison@rvc.ac.uk>; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org>; pgsql-sql
Stuart,
> Just doing a bit of PL/PGSQL so my first question is:
>
> 1) should PL/PGSQL questions be posted to the general mailing list,
> the sql
> mailing list or both?
The SQL list is the best place.
> 2) I am looking for an elegant way of copying a rowtype variable:
>
> eg.
>
> DECLARE
> current_row orf%ROWTYPE;
> previous_row orf%ROWTYPE;
> BEGIN
>
> LOOP
> -- 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.
Another thing to try is, instead of a simple variable assignment,
SELECT current_row INTO previous_row;
... and see if that works. I'll tinker later today; there has to be a
way to do it.
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Copying a rowtype variable.
@ 2002-11-06 21:30 Rison, Stuart <srison@rvc.ac.uk>
2002-11-07 16:28 ` Re: Copying a rowtype variable. Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Rison, Stuart @ 2002-11-06 21:30 UTC (permalink / raw)
To: pgsql-sql; +Cc: Josh Berkus <josh@agliodbs.com>
>> 2) I am looking for an elegant way of copying a rowtype variable:
>>
>> eg.
>>
>> DECLARE
>> current_row orf%ROWTYPE;
>> previous_row orf%ROWTYPE;
>> BEGIN
>>
>> LOOP
>> -- 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Copying a rowtype variable.
2002-11-06 21:30 Re: Copying a rowtype variable. Rison, Stuart <srison@rvc.ac.uk>
@ 2002-11-07 16:28 ` Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-11-07 16:28 UTC (permalink / raw)
To: Rison, Stuart <srison@rvc.ac.uk>; +Cc: pgsql-sql; 'Josh Berkus ' <josh@agliodbs.com>
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:
>
> >> 2) I am looking for an elegant way of copying a rowtype variable:
> >>
> >> eg.
> >>
> >> DECLARE
> >> current_row orf%ROWTYPE;
> >> previous_row orf%ROWTYPE;
> >> BEGIN
> >>
> >> LOOP
> >> -- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Copying a rowtype variable.
@ 2002-11-07 16:51 Rison, Stuart <srison@rvc.ac.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Rison, Stuart @ 2002-11-07 16:51 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql; Josh Berkus <josh@agliodbs.com>
Josh has submitted an appropriate report to pgsql-bugs... so perhaps it will
happen!
Stuart.
> -----Original Message-----
> From: Jean-Luc Lachance [mailto:jllachan@nsd.ca]
> Sent: 07 November 2002 16:29
> To: Rison, Stuart
> Cc: ''pgsql-sql@postgresql.org' '; 'Josh Berkus '
> Subject: Re: [SQL] Copying a rowtype variable.
>
> 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:
>
> >> 2) I am looking for an elegant way of copying a rowtype variable:
> >>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-11-07 16:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-05 13:33 Copying a rowtype variable. Rison, Stuart <srison@rvc.ac.uk>
2002-11-05 16:55 ` Josh Berkus <josh@agliodbs.com>
2002-11-06 21:30 Re: Copying a rowtype variable. Rison, Stuart <srison@rvc.ac.uk>
2002-11-07 16:28 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-11-07 16:51 Re: Copying a rowtype variable. Rison, Stuart <srison@rvc.ac.uk>
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