help get me off the list or at least set the damn thing to no-mai l

-----Original Message-----
From: Frank Miles [mailto:fpm(at)u(dot)washington(dot)edu]
Sent: Tuesday, October 24, 2000 1:56 PM
To: pgsql-general(at)hub(dot)org
Subject: [GENERAL] newbie debugging pl/pgsql : better way?

I have a simple function that isn't working. I've enabled debugging,
but the error message is still mystifying. My function is as follows:

----------------
CREATE FUNCTION proj_name(int) RETURNS char(7) AS '
DECLARE
prn ALIAS FOR $1;
prec record;
year_dig char(2);
index_char char(2);
result char(7);
BEGIN
SELECT INTO prec * FROM project WHERE proj_id = prn;
IF NOT FOUND THEN
RAISE EXCEPTION ''project not found'', prn;
END IF;
year_dig := substr(date_part(''year'', CAST(prec.datein AS
DATE)),3);
IF (index_char < 10)
index_char := lpad(CAST(prec.index_num AS TEXT),
''0'', 2);
ELSE
index_char := CAST(prec.index_num AS TEXT);
END IF;
result := rpad(rpad(rpad(year_dig, 3, ''-''), 5,
upper(prec.picode)), 7, index_char);
RETURN result;
END;
' LANGUAGE 'plpgsql';
----------------

On running it:
ERROR: parser: parse error at or near "$2"

Huh? $2 ???

The debug logs show a bit more:

----------------
ProcessQuery
query: SELECT * FROM project WHERE proj_id = $1
query: SELECT NOT $1
query: SELECT substr(date_part('year', CAST( $1 AS DATE)),3)
query: select date_part($1, timestamp($2))
query: select substr($1, $2, -1)
query: SELECT ( $1 < 10) $2 := lpad(CAST( $3 AS TEXT), '0', 2)
ERROR: parser: parse error at or near "$2"
DEBUG: Last error occured while executing PL/pgSQL function proj_name
DEBUG: line 13 at if
AbortCurrentTransaction
----------------

I presume that the $ arguments are a mechanism for automatic variables.
What concerns me is that AFAICT $1 is used both for the argument
and for internal processing. Even the data type is not preserved.

Is there a better way to debug pl/pgsql functions?

TIA...
-frank