Skip site navigation (1) Skip section navigation (2)

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

From: "Alex Weslowski" <aweslowski(at)rpa(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2451: Short column names return no values within function
Date: 2006-05-23 03:27:01
Message-ID: 200605230327.k4N3R1lL004848@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2451
Logged by:          Alex Weslowski
Email address:      aweslowski(at)rpa(dot)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');

Responses

pgsql-bugs by date

Next:From: Jagadish Prasath RamuDate: 2006-05-23 06:14:34
Subject: Re: [BUGS] BUG #2444: XA Connections given by posgres driver has
Previous:From: Bruce MomjianDate: 2006-05-22 20:33:04
Subject: Re: BUG #2450: change the type of existing column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group