Re: Encountering NULLS in plpgsql

dev(at)archonet(dot)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(at)ieee(dot)org> % *A New World Record*, ELO
http://home.earthlink.net/~yatescr