Bug and/or feature? Complex data types in tables...

From: "Chris Travers" <chris(at)travelamericas(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Bug and/or feature? Complex data types in tables...
Date: 2003-12-31 04:16:12
Message-ID: 000901c3cf8e$28884230$3f285e3d@winxp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2003-12-31 04:25:08 Re: PostgreSQL speakers needed for OSCON 2004
Previous Message Bruce Momjian 2003-12-31 03:53:52 Re: PostgreSQL speakers needed for OSCON 2004

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-12-31 04:19:47 Re: Preventing stack-overflow crashes (improving on max_expr_depth)
Previous Message Bruce Momjian 2003-12-31 03:53:52 Re: PostgreSQL speakers needed for OSCON 2004