Re: Encountering NULLS in plpgsql

dev@archonet.com (Richard Huxton) writes:

> Randy Yates wrote:
>> I wrote a simple pl to compute running sums, but when it encountered a
>> null on a float4 value it caused pgadminIII to crash (exited
>> abruptly). Is this intended behavior?
>
> No, but we'll need more information to figure out what is going on.

Sure - see below.

> What language did you use for the procedural code?
> Can you show us that code?

CREATE TYPE vewCheckingRow AS (
    "fTransactionID" integer,
    "fType" character varying(20),
    "fDate" timestamp with time zone,
    "fDescription" character varying(100),
    "fAmount" numeric(11,2),
    "fClear" boolean,
    "fBookBalance" numeric(11,2),
    "fStatementBalance" numeric(11,2)
);

CREATE OR REPLACE FUNCTION fcnCheckingRow () RETURNS SETOF vewCheckingRow AS $$
DECLARE
    inprow  RECORD;
    outrow  vewCheckingRow;
BEGIN
    outrow."fBookBalance" := 0.0;
    outrow."fStatementBalance" := 0.0;
    FOR inprow IN
        SELECT * FROM "tblChecking" ORDER BY "fTransactionID"
    LOOP
        outrow."fTransactionID" := inprow."fTransactionID";
        outrow."fType" := inprow."fType";
        outrow."fDate" := inprow."fDate";
        outrow."fDescription" := inprow."fDescription";
        outrow."fAmount" := inprow."fAmount";
        outrow."fBookBalance" := outrow."fBookBalance" + inprow."fAmount";
        IF inprow."fClear" <> '0' THEN
            outrow."fClear" := TRUE;
            outrow."fStatementBalance" := outrow."fStatementBalance" + inprow."fAmount";
        ELSE
            outrow."fClear" := FALSE;
        END IF;
        RETURN NEXT outrow;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW vewChecking AS SELECT * FROM fcnCheckingRow();

> What version of postgreSQL?

 PostgreSQL 8.0.3 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 4.0.0 20050505 (Red Hat
4.0.0-4)

> What version of pgAdmin III?

1.4.1 (under FC4/i386)

> Was there any error message?

Not that I saw.

> Is there anything in the logs?

Here's pgsql/data/pg_log/postgresql-Thu.log, when the error would've happened:

ERROR:  relation "tblchecking" does not exist
ERROR:  relation "tblchecking" does not exist
ERROR:  column "ftransactionid" does not exist
ERROR:  operator does not exist: double precision * character
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
ERROR:  column "float" does not exist
ERROR:  column "float8" does not exist
ERROR:  column "float8::fClear" does not exist
ERROR:  column "float::fClear" does not exist
ERROR:  column "tblChecking.fTransactionID" must appear in the GROUP BY clause or be used in an aggregate function
ERROR:  column "tblChecking.fTransactionID" must appear in the GROUP BY clause or be used in an aggregate function
ERROR:  column "tblChecking.fTransactionID" must appear in the GROUP BY clause or be used in an aggregate function
FATAL:  database "rtpfcuasd" does not exist
ERROR:  row "outrow" has no field "fbookbalance"
CONTEXT:  compile of PL/pgSQL function "fcncheckingrow" near line 5
ERROR:  function fcncheckingrow() does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
ERROR:  syntax error at or near "LOOP" at character 973
ERROR:  function fcncheckingrow() does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
ERROR:  syntax error at or near "LOOP" at character 970
ERROR:  function fcncheckingrow() does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
ERROR:  syntax error at or near "TYPE" at character 19
ERROR:  syntax error at or near "LOOP" at character 892
ERROR:  function fcncheckingrow() does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
ERROR:  syntax error at or near "TYPE" at character 19
ERROR:  syntax error at or near "NEXT" at character 872
ERROR:  function fcncheckingrow() does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
ERROR:  syntax error at or near "TYPE" at character 19
LOG:  unexpected EOF on client connection
LOG:  unexpected EOF on client connection
LOG:  unexpected EOF on client connection
LOG:  unexpected EOF on client connection
LOG:  unexpected EOF on client connection
LOG:  unexpected EOF on client connection
LOG:  unexpected EOF on client connection

Were there other log files that may help? Please specify where they would be.
--
%  Randy Yates                  % "Watching all the days go by...
%% Fuquay-Varina, NC            %  Who are you and who am I?"
%%% 919-577-9882                % 'Mission (A World Record)',
%%%% <yates@ieee.org>           % *A New World Record*, ELO
http://home.earthlink.net/~yatescr