开发者

PL/PgSQL confusing error

I have written the following function for Postgres and it works fine on my local windows machine when installed via pgAdmin. When I try to add it to my linux based sever install it throws compile error:

QUERY: SELECT $1 (split_part( $2,' ', $3 )) CONTEXT: SQL statement in PL/PgSQL function "splitwords" near line 34

CREATE OR REPLACE FUNCTION splitwords(text, int)
  RETURNS text AS
$BODY$
DECLARE
inwords ALIAS FOR $1;
posn INTEGER;
existcount INTEGER;
incurrdataid ALIAS FOR $2;
currdataid INTEGER;
currwordid INTEGER;
length INTEGER;
wordpos INTEGER;
newword TEXT;
BEGIN
currdataid:=incurrdataid;
currdataid:=currdataid-1; --corrects for auto-increment error
posn:=1;
WHILE posn<11 LOOP
IF split_part(inwords,' ',posn)='' THEN
-- If no more words are available
    EXIT;
ELSE
--If not at the end of the words
    IF (SELECT wordID FROM words WHERE word=split_part(inwords,' ',posn))>0 THEN
    --If word is already in lexicon
        currwordid:=(SELECT wordID FROM words WHERE word=split_part(inwords,' ',posn))::INTEGER;
        existcount:= (SELECT count FROM words WHERE word=split_part(inwords,' ',posn))::INTEGER;
        UPDATE words SET count=existcount+1 WHERE word=split_part(inwords,' ',posn);
        INSERT INTO wordsdata(wor开发者_如何转开发did,dataid) VALUES (currwordid,currdataid);
        posn:=posn+1;
    ELSE
    --If word is new
        newword=split_part(inwords,' ',posn);
        INSERT INTO words(word,count) VALUES (newword,1);
        currwordid:=(SELECT wordID FROM words WHERE word=split_part(inwords,' ',posn))::INTEGER;
        INSERT INTO wordsdata(wordid,dataid) VALUES (currwordid,currdataid);
        length:=length(split_part(inwords,' ',posn));
        wordpos:=1;
        WHILE wordpos<(length+1) LOOP
            INSERT INTO searchchar(searchstr,wordid) VALUES (substring(split_part(inwords,' ',posn),1,wordpos),currwordid);
            wordpos=wordpos+1;
        END LOOP;
        posn:=posn+1;
    END IF;
END IF;
END LOOP;

RETURN 'rows added';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Had a trawl of google and the documentation but I can't find anything relevant. I am very confused! Any help you may be able to offer much appreciated!


Haven't read your function but the error message usually means that a variable has the same name as a column. Prefixing variable names with a _ usually is a good means to avoid the message, and it makes plpgsql functions a bit more readable.

Also, there's at least one occurrence of = in your code that looks like it should be a :=. plpgsql is forgiving for this stuff, but you shouldn't count on it.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜