Run plpgsql program to update the data in table

2019-09-09 13:53发布

问题:

I am using Postgres 8.4. I want to update the data from using plpgsql and a cursor. When I try to run the plpgsql it generates an error.

CREATE OR REPLACE FUNCTION updateScore() 
RETURNS void AS
$$
DECLARE
singleTopicCriteriaPercentage   DECIMAL(6,10);
sitePercentage          DECIMAL(6,10);
singleSiteCriteriaPercentage    DECIMAL(6,10);
totalSocre          DECIMAL(6,10);

cursor1 CURSOR FOR select id from sitereviews order by id;
cursor2 CURSOR FOR select weight into rating from sitereviews_ratingcriteria where site_id = id;

id              sitereviews.id%TYPE;
weight              sitereviews_ratingcriteria.weight%TYPE;

BEGIN
singleTopicCriteriaPercentage := (10 / 120) * 100;
sitePercentage : 0.0;
singleSiteCriteriaPercentage := 0.0;
totalSocre := 0.0;

OPEN cursor1;
LOOP
FETCH cursor1 INTO id;
EXIT WHEN NOT FOUND;
    totalSocre := 0.0;

    OPEN cursor2;
    LOOP
    FETCH cursor2 INTO weight;
    EXIT WHEN NOT FOUND;
        sitePercentage := singleTopicCriteriaPercentage * weight;
        singleSiteCriteriaPercentage :=  (sitePercentage / 100) * 10;
        totalSocre := singleSiteCriteriaPercentage + totalSocre;
    END LOOP;
    CLOSE cursor2;

    update sitereviews set weight = : round(totalSocre)  WHERE CURRENT OF cursor1;
END LOOP
CLOSE cursor1;
END;
$$ LANGUAGE 'PLPGSQL'

following is the error when try to run this program :

ERROR: could not load library "C:/Program Files/PostgreSQL/8.4/lib/plpgsql.dll": The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail.
 SQL state: 58P01

回答1:

It is look so your PostgreSQL installation is not complete or it is broken. In older PostgreSQL installators plpgsql was optional. So, please, check your installation.