Hello folks,
I wrote my first plpgsql-functions for PostgreSQL 7.1.3. I try to
convert Ascii-Strings in HTML-conform Strings (Converting 'Äquator' ->
'Äquator') ... Now I get a error message like this:
NOTICE: plpgsql: ERROR during compile of f_ascii2html near line 7
ERROR: $1 is declared CONSTANT
For me $1, or better InpAscii is not CONSTANT ... I cannot find the
mistake ... Can anybody help out?
Hopefully, you can reproduce the error with the code right here.
Thanks a lot,
Hans
PS:
There is some debug-code that I have not used because the functions does
not work ;-)).
-- BEGIN OF SKRIPT ...
DROP TABLE t_ascii2html;
/* table for replacing letters */
CREATE TABLE t_ascii2html (
ascii VARCHAR(1),
html VARCHAR(20)
);
INSERT INTO t_ascii2html VALUES ('ä','ä');
INSERT INTO t_ascii2html VALUES ('ö','ö');
INSERT INTO t_ascii2html VALUES ('ü','ü');
INSERT INTO t_ascii2html VALUES ('Ä','ä');
INSERT INTO t_ascii2html VALUES ('Ö','ö');
INSERT INTO t_ascii2html VALUES ('Ü','ü');
INSERT INTO t_ascii2html VALUES ('ß','ß');
INSERT INTO t_ascii2html VALUES ('"','"');
INSERT INTO t_ascii2html VALUES ('&','&');
INSERT INTO t_ascii2html VALUES ('<','<');
INSERT INTO t_ascii2html VALUES ('>','>');
DROP FUNCTION f_ascii2html(TEXT);
/* Converting 'special' letters (eg. german umlaute like "ö") into a
HTML-conform string */
CREATE FUNCTION f_ascii2html(TEXT)
RETURNS TEXT
AS '
DECLARE
InpAscii ALIAS FOR $1;
CharMap RECORD;
InsertPosition INTEGER;
Part1 TEXT;
Part2 TEXT;
BEGIN
InpAscii := $1;
-- Select all datasets from the table describing the replacement
FOR CharMap IN SELECT * FROM f_ascii2html LOOP
RAISE NOTICE ''CharMap --- ASCII: %, HTML: %'', t_ascii2html.ascii,
t_ascii2html.html;
WHILE InpAscii ~ CharMap.ascii
RAISE NOTICE ''INPASCII: %'', InpAscii;
SELECT position(InpAscii IN CharMap.ascii)
INTO InsertPosition;
RAISE NOTICE ''INSERTPOSITION: %'', InsertPosition;
SELECT substrg(InpAscii FROM (InsertPosition - 1))
INTO Part1;
RAISE NOTICE ''PART1: %'', Part1;
SELECT substrg(InpAscii FROM (InsertPosition + 1))
INTO Part2;
RAISE NOTICE ''PART2: %'', Part2;
InpAscii := Part1 || CharMap.html || Part2 ;
RAISE NOTICE ''INPASCII: %'', InpAscii;
END LOOP;
END LOOP;
RAISE NOTICE ''InpAscii: %'', InpAscii;
RETURN InpAscii
END;
'
LANGUAGE 'plpgsql';
/* Sample: Converting 'Äquator' -> 'Äquator' */
select f_ascii2html('Äquator');
/* I get the following error message:
NOTICE: plpgsql: ERROR during compile of f_ascii2html near line 7
ERROR: $1 is declared CONSTANT
*/