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