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