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