Thread: Updating values of a record variable

Hi,

I would like to populate a newly added column with count information generated from another table. I tried to do this with the following procedure.

It should run thru every row of lr_concepts and count the number of related records in the acs_rels table. The value of the count should be written into the column “related_lr” of the table lr_concepts. Unfortunately this does not work as I thought.

First I tried to manipulate the lr_concepts table with an update statement within the loop part. But this ended in an infinite loop.

Now I tried to change the value of the record variable, but that did not help, cause the value is not written to the table obviously.

What can I do to make this work ?

Many TIA,

Peter Alberer

----------------------------------------------------

create or replace function test1 ()

returns integer as '

declare

    object_rec      record;

    v_count         integer;

    v_count2        integer;

begin

    v_count2 := 1;

    for object_rec in select lr_concept_id,name,related_lr from lr_concepts

    loop

        Raise Notice ''Checking concept %'',object_rec.name;

        Raise Notice ''Position %'',v_count2;

        v_count2 := v_count2 + 1;

        select count(*) into v_count from

        acs_rels where rel_type=''lr_concept_rel'' and object_id_one = object_rec.lr_concept_id;

        Raise Notice ''found % children'',v_count;

        update lr_concepts set related_lr = v_count where

        lr_concept_id = object_rec.lr_concept_id;    end loop;

    return 1;

end;

' language 'plpgsql';