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

2006-05-23 Thread Alex Weslowski 

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

[BUGS] missing or erroneous pg_hba.conf file

2006-05-23 Thread qinyan 
 你好：
  
  我是 一名中国学生，正在做网格方面的东西，做到最后当我执行命令时，显示如下

2006-05-21 20:19:21,650 ERROR service.ReliableFileTransferImpl
[main,:73]Unable to setup database driver with pooling.A
connection error has occurred: FATAL:  missing or erroneous
pg_hba.conf file

2006-05-21 20:19:23,213 WARN  service.ReliableFileTransferHome
[main,initialize:97] All RFT requests will fail and all GRAM jobs that
require file staging willfail.A connection error has occurred:
FATAL:  missing or erroneous pg_hba.conf file
   
  我知道 这个是说数据库没有连接好，但是pg_hba.conf 文件我已经加了一行
host    rftDatabase  globus
192.168.0.99/32  
18942670    ，而且要求改的

jndi-config.xml ，文件我也将相应的改掉了。

现在数据库连接不上，不知道该怎么办？

请问是什么原因？

期待你们的回复
  

	你 不 想 试 试 今 夏 最 “酷” 的 邮 箱 吗 ？
	
	 蕴 涵 中 华 传 统 文 化 于 世 界 一 流 科 技 之 中，创 新 Ajax 技 术，126 “D 计 划”火 热 体 验 中 ！

Re: [BUGS] BUG #2451: Short column names return no values within function

2006-05-23 Thread Tom Lane 
"Alex Weslowski" <[EMAIL PROTECTED]> writes:
> DECLARE
>   peg VARCHAR(5) := '';
>   rs03 VARCHAR(3) := '';
>   rs12 VARCHAR(3) := '';
>   rec RECORD;
> BEGIN
>   FOR rec IN SELECT Symbol, RS03, RS12, Peg
>   FROM TestBug WHERE Date=d
>   ORDER BY RS12 DESC LOOP

The problem is that you've got local variables shadowing the field names
you want to use.  What the SQL engine sees from that is

SELECT Symbol, $1, $2, $3
FROM TestBug WHERE Date=$4
ORDER BY $5 DESC 

and it just assigns random ?columnN? names to the record columns (which
are going to have useless empty-string values anyway).

Use different local variable names, or qualify the field references in
the SELECT, eg TestBug.RS03.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [BUGS] BUG #2451: Short column names return no values within function

2006-05-23 Thread Michael Fuhr 
On Tue, May 23, 2006 at 03:27:01AM +, Alex Weslowski wrote:
> 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. 

The function declares variables with the same names as table columns;
that makes queries like "SELECT Symbol, RS03, RS12, Peg ..." ambiguous
because it's not clear whether those names refer to columns or to
variables.

> 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.

Type conversion and label length aren't relevant -- the problem is
due to using the same label to refer to multiple things.  Use different
names for the variables or qualify the column names in the query
("SELECT t.symbol, t.rs03, t.rs12, t.peg FROM testbug AS t ...").

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq