Thread: 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

* 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

Ashley Clark <aclark@ghoti.org> writes:
>> Is there a better way to debug pl/pgsql functions?

> If you find it let me know.

The postmaster log should have useful tidbits.  Make sure there *is*
a postmaster log --- start the postmaster without -S, and with its
stdout and stderr directed into a logfile in some handy place.

Then run the misbehaving application with debug options set to -d2
or higher, eg
    export PGOPTIONS="-d2"
    psql mydb
    mydb> -- do your worst here

The log will show the primitive SQL queries generated by plpgsql
functions, as well as the error messages from them.  This is usually
a large leg up on figuring out the problem.  If you're still stuck,
post the plpgsql function body and the relevant logfile section on
the pgsql mail lists, and someone will probably be able to help.

            regards, tom lane