newbie debugging pl/pgsql : better way?

From: Frank Miles <fpm(at)u(dot)washington(dot)edu>
To: pgsql-general(at)hub(dot)org
Subject: newbie debugging pl/pgsql : better way?
Date: 2000-10-24 18:56:10
Message-ID: Pine.A41.4.21.0010241147330.187564-100000@mead2.u.washington.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marten Feldtmann 2000-10-24 19:12:01 Re: Index on substring?
Previous Message Ron Chmara 2000-10-24 18:03:54 Re: phpPgAdmin