错误和/或功能？表格中的复杂数据类型...... [英] Bug and/or feature? Complex data types in tables...
问题描述
大家好;
我刚做了一个有趣的发现。不确定它是好东西还是
不是，并且使用它肯定会破坏第一个正常形式....甚至不确定它是否真的有效。但是，因为我能够破坏后端，所以在某处有一个错误
...
test = #select version（）;
版
------------------------------- ---------------------------------------------
----
-----
i6486-pc-cygwin上的PostgreSQL 7.4，由GCC gcc（GCC）3.3.1编译（cygming < br $>
spec
ial）
（1行）
请尝试以下示例：
CREATE TABLE test1（
test_id SERIAL，
test_text TEXT
）;
CREATE TABLE test2（
other_test test1，
test_text text
）;
创建表没有任何问题。当然没有办法将任何东西插入表中，你编写一个函数来创建数据
类型。所以我创建了以下函数：
CREATE FUNCTION test1（int，text）返回test1为''
声明retval test1;
开始
retval.test_id：= $ 1;
retval.test_text：= $ 2;
返回retval;
end;
''语言plpgsql。
现在我可以插入表中了。但是我无法从
表中得到任何东西！如果我从test2尝试一个简单的
SELECT *;
我得到：错误：无法显示类型记录的值
所以，我想我会编写一个函数来将记录转换为文本。我写的
函数是：
CREATE FUNCTION test1_to_text（test1）返回文本为''
声明retval文本;
开始
retval：= test1.test_id;
retval：= retval :: text;
retval：= retval || ''''：'''';
retval：= retval || test1.test_text;
返回retval;
结束;
''语言plpgsql;
这是发生崩溃的地方（在短暂挂起之后）：
test =＃select test1_to_text（other_test）来自test2;
服务器意外关闭了连接
这可能意味着服务器在处理请求之前或处理时异常终止了
。
有趣的是我能做到：
test = #select test1_to_text（test1（''1''，''hi there''））;
test1_to_text
---------- -----
1：你好
（1排）
祝福，
Chris Travers
---------------------------（播出结束） - -------------------------
提示2：你可以使用unregister命令立即取消所有列表
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）
1;
retval.test_text：=
2;
返回retval;
结束;
''语言plpgsql。
现在我可以插入表中了。但是我无法从
表中得到任何东西！如果我从test2尝试一个简单的
SELECT *;
我得到：错误：无法显示类型记录的值
所以，我想我会编写一个函数来将记录转换为文本。我写的
函数是：
CREATE FUNCTION test1_to_text（test1）返回文本为''
声明retval文本;
开始
retval：= test1.test_id;
retval：= retval :: text;
retval：= retval || ''''：'''';
retval：= retval || test1.test_text;
返回retval;
结束;
''语言plpgsql;
这是发生崩溃的地方（在短暂挂起之后）：
test =＃select test1_to_text（other_test）来自test2;
服务器意外关闭了连接
这可能意味着服务器在处理请求之前或处理时异常终止了
。
有趣的是我能做到：
test = #select test1_to_text（test1（''1''，''hi there''））;
test1_to_text
---------- -----
1：你好
（1排）
祝福，
Chris Travers
---------------------------（播出结束） - -------------------------
提示2：你可以使用unregister命令立即取消所有列表
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）
" Chris Travers" < CH *** @ travelamericas.com>写道：尝试以下示例：
CREATE TABLE test1（
test_id SERIAL，
test_text TEXT
）;
CREATE TABLE test2（
other_test test1，
test_text text
）;
实际上这应该是不允许的，我认为。回到Berkeley Postgres的
之前的SQL时代，实际上有一个功能涉及以这种方式声明
表列，但它没有按你想象的那样工作; - ），并且
无论如何它已被打破多年。
我不知道为什么我们从来没有迈出这一步防止复杂类型
被声明为其他类型的字段。我想有一些
认为我们最终会支持它，但我不相信那天
天真的很接近。
问候，汤姆小巷
------------------------- - （播出结束）---------------------------
提示8：解释分析是你的朋友
Hi all;
I just made an interesting discovery. Not sure if it is a good thing or
not, and using it certainly breakes first normal form.... Not even sure if
it really works. However, as I am able to CRASH the backend, there is a bug
here somewhere...
test=# select version();
version
----------------------------------------------------------------------------
----
-----
PostgreSQL 7.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1 (cygming
spec
ial)
(1 row)
Try the following example:
CREATE TABLE test1 (
test_id SERIAL,
test_text TEXT
);
CREATE TABLE test2 (
other_test test1,
test_text text
);
The table is created without any problem. Of course there is no way of
inserting anything into the table, you write a function to create the data
type. So I created the following function:
CREATE FUNCTION test1 (int, text) returns test1 as ''
declare retval test1;
begin
retval.test_id := $1;
retval.test_text := $2;
return retval;
end;
'' language plpgsql.
Now I can insert into the table. But I cannot get anything out of the
table! If I try a simple
SELECT * from test2;
I get: ERROR: cannot display a value of type record
So, I figured I would write a function to turn the record into text. The
function I wrote is:
CREATE FUNCTION test1_to_text(test1) returns text as ''
declare retval text;
begin
retval := test1.test_id;
retval := retval::text;
retval := retval|| '''':'''';
retval := retval|| test1.test_text;
return retval;
end;
'' language plpgsql;
Here is where the crash occurs (after a brief hang):
test=# select test1_to_text(other_test) from test2;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Interestingly I can do:
test=# select test1_to_text(test1(''1'', ''hi there''));
test1_to_text
---------------
1:hi there
(1 row)
Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
解决方案1;
retval.test_text :=
2;
return retval;
end;
'' language plpgsql.
Now I can insert into the table. But I cannot get anything out of the
table! If I try a simple
SELECT * from test2;
I get: ERROR: cannot display a value of type record
So, I figured I would write a function to turn the record into text. The
function I wrote is:
CREATE FUNCTION test1_to_text(test1) returns text as ''
declare retval text;
begin
retval := test1.test_id;
retval := retval::text;
retval := retval|| '''':'''';
retval := retval|| test1.test_text;
return retval;
end;
'' language plpgsql;
Here is where the crash occurs (after a brief hang):
test=# select test1_to_text(other_test) from test2;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Interestingly I can do:
test=# select test1_to_text(test1(''1'', ''hi there''));
test1_to_text
---------------
1:hi there
(1 row)
Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
"Chris Travers" <ch***@travelamericas.com> writes:Try the following example: CREATE TABLE test1 (
test_id SERIAL,
test_text TEXT
); CREATE TABLE test2 (
other_test test1,
test_text text
);
This should in fact be disallowed, I think. Back in the pre-SQL days of
Berkeley Postgres, there actually was a feature that involved declaring
table columns this way, but it did NOT work the way you think ;-), and
in any case it has been broken for many years.
I''m not sure why we''ve never taken the step of preventing complex types
from being declared as fields of other types. I suppose there''s some
thought that we''ll eventually support it, but I don''t believe that that
day is real close.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
这篇关于错误和/或功能？表格中的复杂数据类型......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
