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.
精彩评论