Re: newbie debugging pl/pgsql : better way?

* Frank Miles in "[GENERAL] newbie debugging pl/pgsql : better way?"
* dated 2000/10/24 11:56 wrote:

> 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;
^^^^^^^^^^^^^^
Well, here's part of the problem, wrong syntax, select * into prec from
...

> IF NOT FOUND THEN
> RAISE EXCEPTION ''project not found'', prn;

If I'm not mistaken you should specify a format string '%' somewhere
for prn to actually be displayed, eg, raise exception ''blah %'', foo;

> 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);

Hmmm, where does index_char get initialized?

> 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"

I think your select is doing that, but there are other potential
problems as noted above.

> 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.

Unfortunately I can't answer this question.

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

If you find it let me know.

--
shaky cellar