Date: Thu, 3 Jun 2004 13:41:44 -0700
From: elein <elein@varlena.com>
To: Enrico Weigelt <weigelt@metux.de>
Cc: postgresql advocacy <pgsql-advocacy@postgresql.org>,
	elein <elein@varlena.com>
Subject: Re: [SQL] SQL Spec Compliance Questions
Message-ID: <20040603134144.X11485@cookie.varlena.com>
Mail-Followup-To: Enrico Weigelt <weigelt@metux.de>,
	postgresql advocacy <pgsql-advocacy@postgresql.org>
References: <200403302050.12415.josh@agliodbs.com>
	<406D1031.7010603@joeconway.com>
	<200404020838.22698.josh@agliodbs.com>
	<406D9943.8070401@joeconway.com> <1129.1080928076@sss.pgh.pa.us>
	<20040603171905.GI32598@nibiru.metux.de>
	<20040603110657.W11485@cookie.varlena.com>
	<20040603184526.GK32598@nibiru.metux.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20040603184526.GK32598@nibiru.metux.de>
User-Agent: Mutt/1.3.22.1i

A typed table is an type which happens to be
a table.  They are also known as composite types
or row types.

We have these.

	CREATE TYPE deptavgs AS ( yr int, mon int, minsal int, maxsal int, avgsalary int8);

In PostgreSQL you can use these table types in order
to return tuples from plpgsql functions.

 CREATE or REPLACE FUNCTION avgdept() RETURNS deptavgs AS
   '
   DECLARE
      r deptavgs%rowtype;

   ...
      return r;
   ...

This should also allow you to do a:
	create table foo as deptavgs;

If I recall correctly, PostgreSQL does not
support this syntax.  But I'm not sure.

The concept is only tricky when you distinguish
between a row which is like a 1 dimensional array 
and a table which is 2 dimensional.  The row returning
functions return a row at a time.

There is also the concept of an unnamed row type
which is the temporary type of a result of a select.

In both Illustra and Informix IUS, the row type
was treated as a first class SQL type in that you
could create tables containing tables.  PostgreSQL
does not support this.

create table dept_aggs (
	deptid	integer,
	salavgs	deptavgs,
	...
);

The elements of the salavgs column are accessible
with the following syntax. (My memory may be a bit
faulty on this one.)

dep_aggs == table
dep_aggs.deptid == table.column
dep_aggs.salavgs == table.table
dep_aggs.salavgs.minsal == table.column

You could do a 
	select salvags.* from dep_aggs where dep_aggs.dept_id = 1;

The return values would be of the type deptavgs.

I'm sure this is more than what was asked.  I reviewed
a review of the SQL2003 standards with regards to PostgreSQL
in General Bits Issue #71 and #73.
	http://www.varlena.com/GeneralBits/71
	http://www.varlena.com/GeneralBits/72


--elein
============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com

          PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.


On Thu, Jun 03, 2004 at 08:45:27PM +0200, Enrico Weigelt wrote:
> * elein <elein@varlena.com> wrote:
> 
> Hi,
> 
> > I missed the first part of this question.
> > But perhaps I can explain the answer if I heard
> > the whole question. Both Illustra (postgres) and Informix
> > implemented typed tables.
> What exactly does 'typed tables' mean ?
> Someone here on the list (dont remember who it was ...), sayd
> something about derived tables. This works fine w/ psql.
> 
> 
> cu
> -- 
> ---------------------------------------------------------------------
>  Enrico Weigelt    ==   metux IT service
> 
>   phone:     +49 36207 519931         www:       http://www.metux.de/
>   fax:       +49 36207 519932         email:     contact@metux.de
>   cellphone: +49 174 7066481
> ---------------------------------------------------------------------
>  -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
> ---------------------------------------------------------------------
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)