Re: [SQL] SQL Spec Compliance Questions - Mailing list pgsql-advocacy

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)