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
