Syntax error 42601 when trying to run plpgsql function

Question

I'm new to PL/pgSQL. While trying to insert data into tables the following function generates an error. This link defines the error, but I can't understand this link problem.

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

 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;
 weights         sitereviews_ratingcriteria.weight%TYPE;

 BEGIN
 singleTopicCriteriaPercentage := (10.0 / 120.0) * 100.0;
 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 weights;
EXIT WHEN NOT FOUND;
    sitePercentage := singleTopicCriteriaPercentage * weights;
    singleSiteCriteriaPercentage :=  (sitePercentage / 100) * 10;
    totalSocre := singleSiteCriteriaPercentage + totalSocre;
END LOOP;
CLOSE cursor2;

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

following is the compile time error:

ERROR:  syntax error at or near "$1"
LINE 1: update sitereviews set  $1  := round( $2 ) WHERE CURRENT OF ...
                            ^
QUERY:  update sitereviews set  $1  := round( $2 ) WHERE CURRENT OF  $3 
CONTEXT:  SQL statement in PL/PgSQL function "updatescore" near line 35

********** Error **********

ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "updatescore" near line 35

my column name is weight if , replace weights with weight , there is no action perform. — Harmeet Singh Taara
– Harmeet Singh Taara, Commented May 16, 2013 at 10:57
It is essential to include the version of PostgreSQL in use. Also, you should have learned not to quote the language name plpgsql from the posting you are referring to. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 18, 2013 at 14:09

Bob Jarvis - Слава Україні · Accepted Answer · 2013-05-16 10:50:07Z

2

Change your update statement to

update sitereviews set set weights = round(totalSocre)  WHERE CURRENT OF cursor1

or, basically, drop the ":" before the equal sign. PL/SQL and plpgsql use := for assignment and comparison, but SQL uses =.

Share and enjoy.

answered May 16, 2013 at 10:50

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Bob Jarvis - Слава Україні Over a year ago

Did you commit the changes anywhere? I don't see a COMMIT in the procedure.

Harmeet Singh Taara Over a year ago

where to put the commit statement ?

Bob Jarvis - Слава Україні Over a year ago

Well, that depends on how this procedure is being used. If it's part of a larger unit of work you'd have to analyze the code that invokes it and put the COMMIT where it makes sense to have it - i.e. where the unit of work is complete. If you just need this procedure to do its work and then commit the changes you could try putting a COMMIT at the end of the procedure, after both cursors are closed.

Harmeet Singh Taara Over a year ago

thanks @bob jarvis . please don't laugh , but how to call the procedure ?

Harmeet Singh Taara Over a year ago

is it possible to call the procedure in the same sql script file ?

|

Collectives™ on Stack Overflow

Syntax error 42601 when trying to run plpgsql function

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related