Thread: Copying a rowtype variable.

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
----------------------------------------------------------------------------
--

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

>> 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.

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

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:
> >>