运行plpgsql程序以更新表中的数据 [英] Run plpgsql program to update the data in table

问题描述

我使用Postgres 8.4。我想更新使用plpgsql和游标的数据。当我试图运行plpgsql它生成一个错误。

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

推荐答案

所以你的PostgreSQL安装不完整或它是破碎。在旧的PostgreSQL安装程序中，plpgsql是可选的。因此，请检查您的安装。

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.

这篇关于运行plpgsql程序以更新表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！