Re: [SQL] SQL Spec Compliance Questions

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(at)varlena(dot)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(at)varlena(dot)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(at)metux(dot)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(at)postgresql(dot)org)