-----Original Message----- From: Frank Miles [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 24, 2000 1:56 PM To: [EMAIL PROTECTED] 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
