BUG #2451: Short column names return no values within function

The following bug has been logged online:

Bug reference:      2451
Logged by:          Alex Weslowski
Email address:      aweslowski@rpa.com
PostgreSQL version: 8.1
Operating system:   Windows XP
Description:        Short column names return no values within function
Details:

Below is code for duplicating this error. Fields "Peg" and "Rs03" and "Rs12"
are absent from returned record (either Record or Cursor) even though the
values in the table are not null.

Problem might be related to type conversion (NULL converts to '' which has
no meaning to INT or NUMERIC).

Problem is fixed by renaming columns to "Peg_Ratio" and "RS03RS" and
"RS12RS". So, there is something more going on here, related to length of
column name.


-----


CREATE TABLE TestBug (
    Date DATE,
    Symbol VARCHAR(10),
    Peg NUMERIC(8, 3),
    RS03 SMALLINT,
    RS12 SMALLINT
    );

INSERT INTO TestBug VALUES ('5/18/06', 'ABAX', 1.38, 78, 95);
INSERT INTO TestBug VALUES ('5/18/06', 'IRIX', NULL, 97, 92);
INSERT INTO TestBug VALUES ('5/18/06', 'SCSC', 1.31, 59, 65);

CREATE TYPE row_TestBug AS (
    idx INT,
    str VARCHAR(512)
);

CREATE OR REPLACE FUNCTION fn_TestBug(
    d DATE
    )
RETURNS SETOF row_TestBug AS $$
DECLARE

    varSym VARCHAR(10)  := '';
    peg VARCHAR(5) := '';
    numPeg NUMERIC(8, 3) := NULL;
    varPeg VARCHAR(9) := NULL;
    rs03 VARCHAR(3) := '';
    intRs03 INT := NULL;
    varRs03 VARCHAR(8) := NULL;
    rs12 VARCHAR(3) := '';
    intRs12 INT := NULL;
    varRs12 VARCHAR(8) := NULL;
    str VARCHAR(512) := '';
    i INT := 0;

    rtn row_TestBug;

    rec RECORD;

BEGIN

    FOR rec IN SELECT Symbol, RS03, RS12, Peg
    FROM TestBug WHERE Date=d
    ORDER BY RS12 DESC LOOP

    varSym := rec.Symbol;

    varSym := RTRIM(varSym) || REPEAT(' ', 8 - LENGTH(RTRIM(varSym)));

    rs03 := ' NA';
    IF (rec.RS03 IS NOT NULL) THEN
        IF (rec.RS03 > 0) THEN
            rs03 := CAST(rec.RS03 AS VARCHAR);
        END IF;
    END IF;

    rs12 := ' NA';
    IF (rec.RS12 IS NOT NULL) THEN
        IF (rec.RS12 > 0) THEN
            rs12 := CAST(rec.RS12 AS VARCHAR);
        END IF;
    END IF;

    peg := '  NA';
    IF (rec.Peg IS NOT NULL) THEN
        peg := CAST(CAST(rec.Peg AS NUMERIC(5, 2)) AS VARCHAR);
    END IF;

    str := varSym || ' 3-Mo RS:' || rs03 || '  12-Mo RS:' || rs12 || '  PEG: '
|| peg;

    rtn := ROW(i, str);
    RETURN NEXT rtn;

    i := i + 1;

    END LOOP;

    RETURN;

END;
$$ LANGUAGE plpgsql;


SELECT str FROM fn_TestBug('2006/05/18');