Thread: Updating values of a record variable

Updating values of a record variable

From
"Peter Alberer"
Date:

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

Re: Updating values of a record variable

From
Manfred Koizar
Date:
On Wed, 26 Jun 2002 10:02:43 +0200, "Peter Alberer"
<h9351252@obelix.wu-wien.ac.at> wrote:
>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.

Peter, try:

UPDATE lr_concepts
   SET related_lr = (
           SELECT COUNT(*)
             FROM acs_rels a
            WHERE a.rel_type = 'lr_concept_rel' AND
                  a.object_id_one = lr_concepts.lr_concept_id);

Servus
 Manfred