Bug and/or feature? Complex data types in tables... - Mailing list pgsql-general

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