From: elein <elein@varlena.com>
To: Enrico Weigelt <weigelt@metux.de>
Cc: postgresql advocacy <pgsql-advocacy@postgresql.org>
Cc: elein <elein@varlena.com>
Subject: Re: [SQL] SQL Spec Compliance Questions
Date: Thu, 3 Jun 2004 13:41:44 -0700
Message-ID: <20040603134144.X11485@cookie.varlena.com> (raw)
In-Reply-To: <20040603184526.GK32598@nibiru.metux.de>
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>
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/71http://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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: elein@varlena.com, weigelt@metux.de, pgsql-advocacy@postgresql.org
Subject: Re: [SQL] SQL Spec Compliance Questions
In-Reply-To: <20040603134144.X11485@cookie.varlena.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox