Re: ERROR: $1 is declared CONSTANT in plpgsql - Mailing list pgsql-novice

Once you declare an ALIAS in a function, you can't change the value of it.
If you need to change it, you must copy the value to a variable that you
declare with the appropriate datatype and change that instead.

cheers,
Jason

>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
>*/
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)