The following bug has been logged online: Bug reference: 2451 Logged by: Alex Weslowski Email address: [EMAIL PROTECTED] 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'); ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match