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

On Jan 2, 2004, at 7:44 AM, Chris Travers wrote:

> creating a complex type and using it in a table would create the same
> problem, would it not?
> If my type has more than one component, then it would not work well.

After a bit of experimentation, I see what you mean:
test=# select version();
                                                         version
------------------------------------------------------------------------
-----------------------------------------------
  PostgreSQL 7.4 on powerpc-apple-darwin7.2.0, compiled by GCC gcc (GCC)
3.3 20030304 (Apple Computer, Inc. build 1495)
(1 row)

test=# create type complex_number as (real numeric, imaginary numeric);
CREATE TYPE
test=# create table numbers (num complex_number);
ERROR:  column "num" has composite type complex_number
ERROR:  column "num" has composite type complex_number
test=# create table complex_number_table (real numeric not null,
imaginary numeric not null);
CREATE TABLE
test=# create table numbers (num complex_number_table);
CREATE TABLE

You'd think the first CREATE TABLE numbers would work, and the second
wouldn't. (Unless I'm doing something wrong.)

> Here is a better example.  Imagine creating a type for complex numbers.
> Each complex number has 2 components:  a real component (x, numeric)
> and an
> imaginary component (y, numeric).  The standard representation is  x
> +/- yi,
> so if the real component is 3.4 and the imaginary component is 5, it
> would
> be written 3.4 + 5i.

In the language of Date and Darwen, you're talking about possible
representations, or possreps, I believe. One possible representation of
a complex number would be x +/- yi, another could be (x,y)

> Storing this data in the database would require either:
> 1:  A text string which would be parsed by the app.  (not really very
> useful)
> 2:  A native datatype consisting of 2 numeric components, that could
> be cast
> as text by the rules above.
>
> Obviously the second one is best.

Definitely. The default TIMESTAMP possrep is much different from how
it's represented internally. For that matter, NUMERIC is, too. There's
no reason to necessarily store the value in the form the user sees.

> Currently in PostgreSQL, I would have to
> write this in C, but with complex types, I could write this mostly in
> PLPGSQL!

(To avoid confusion since we're talking about complex numbers, I'm
assuming you mean what PostgreSQL refers to as composite types.) It
definitely would be nice to be able to define composite types that can
be used as attributes and functions. It seems like there's quite a bit
of, er, functionality with composite types already. I don't have
pl/pgsql installed, but I was able to create some simple operators with
just SQL (see below). They're not perfect (and don't let us use
composite types in tables); just exploring what I could do. I wonder
what it would take to allow these user-defined types defined in
PostgreSQL (rather than C) usable in tables.

Michael Glaesemann
grzm myrealbox com

test=# create or replace function THE_REAL(complex_number) returns
numeric as 'select $1.real as real;' language sql;
CREATE FUNCTION
test=# create or replace function THE_IMAGINARY(complex_number) returns
numeric as 'select $1.imaginary as real;' language sql;
CREATE FUNCTION
test=# create function complex_number(numeric,numeric) returns
complex_number as 'select $1,$2;' language sql;
CREATE FUNCTION
test=# select THE_REAL(complex_number(4::numeric,3::numeric));
  the_real
----------
         4
(1 row)

test=# select THE_IMAGINARY(complex_number(4::numeric,3::numeric));
  the_imaginary
---------------
              3
(1 row)

test=# create or replace function
display_ordpair_complex_number(complex_number) returns text as 'select
''('' || $1.real || '','' || $1.imaginary || '')'';' language sql;
CREATE FUNCTION
test=# create function display_irep_complex_number(complex_number)
returns text as 'select $1.real || '' '' || $1.imaginary || ''i'';'
language sql;
CREATE FUNCTION
test=# select
display_ordpair_complex_number(complex_number(4::numeric,3::numeric));
  display_ordpair_complex_number
--------------------------------
  (4,3)
(1 row)

test=# select
display_irep_complex_number(complex_number(4::numeric,3::numeric));
  display_irep_complex_number
-----------------------------
  4 3i
(1 row)