help get me off the list or at least set the damn thing to no-mai l - Mailing list pgsql-general

From Oelkers, Phil
Subject help get me off the list or at least set the damn thing to no-mai l
Date
Msg-id F5F5D222CD70D111886C00805FE69D21024483E4@schexch1
Whole thread Raw
List pgsql-general

-----Original Message-----
From: Frank Miles [mailto:fpm@u.washington.edu]
Sent: Tuesday, October 24, 2000 1:56 PM
To: pgsql-general@hub.org
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


pgsql-general by date:

Previous
From: Vincent Magnin
Date:
Subject: Case Sensitive Table ?
Next
From: Peter Mount
Date:
Subject: RE: [INTERFACES] JDBC DRIVER