Thread: ERROR: $1 is declared CONSTANT in plpgsql
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
*/
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)
Hans Plum <plum@giub.uni-bonn.de> writes:
> DECLARE
> InpAscii ALIAS FOR $1;
> BEGIN
> InpAscii := $1;
> NOTICE: plpgsql: ERROR during compile of f_ascii2html near line 7
> ERROR: $1 is declared CONSTANT
You can't assign to a function's input parameters. That first
assignment is useless as well as incorrect (if it were allowed,
it'd effectively be $1 := $1, because of your ALIAS).
regards, tom lane
On Tue, 2002-05-07 at 12:18, Hans Plum wrote: > 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? ... > CREATE FUNCTION f_ascii2html(TEXT) > RETURNS TEXT > AS ' > DECLARE > InpAscii ALIAS FOR $1; ... > BEGIN > InpAscii := $1; You don't need to (cannot) assign to InpAscii. Just miss out that last line. You have already done what you want in the declaration, so that last line amounts to "$1 := $1". -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Dearly beloved, avenge not yourselves, but rather give place unto wrath. For it is written, Vengeance is mine; I will repay, saith the Lord. Therefore if thine enemy hunger, feed him; if he thirst, give him drink; for in so doing thou shalt heap coals of fire on his head. Be not overcome of evil, but overcome evil with good." Romans 12:19-21
Attachment
I'd guess this line is the problem:
InpAscii := $1;
I believe this parses to
$1:=$1;
and a bit later down:
InpAscii := Part1 || CharMap.html || Part2 ;
would, I believe, parse to
$1:=Part1 || CharMap.html || Part2;
I don't know wether it is permitted to assign values to
the arguments passed (although I'd imagine not),
but I don't do it myself.
Cheers,
- Stuart
> -----Original Message-----
> From: Hans Plum [mailto:plum@giub.uni-bonn.de]
>
> 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
> */