This is definitely a step forward toward tables in columns.
However, running tests against this I am getting weird
results.
select a(myfoo), b(myfoo) from bar;
worked once or twice.
From then on it crashed my server. The log says
it was terminated with signal 11.
This is what I did:
CREATE TABLE foo(a int, b int);
INSERT INTO foo VALUES (1, 2);
INSERT INTO foo VALUES (2, 3);
-- Main uses of pseudo-types
CREATE FUNCTION get_foo1() RETURNS foo
LANGUAGE SQL AS
'SELECT * FROM foo WHERE a = 1';
SELECT b FROM get_foo1();
CREATE TABLE bar(myfoo foo, c int);
insert INTO bar SELECT get_foo1(), 1;
select a(myfoo),b(myfoo) from bar;
select a(myfoo),b(myfoo),c from bar;
The select a(myfoo)... only seemed to
return the first row in the embedded table.
I experimented with different syntax
to see if I could get it to show rows
within the columns and got a few syntax errors.
After that, the original
select a(myfoo), b(myfoo) from bar;
crashed every time.
Let me know if you have an inkling about this.
Otherwise, I will work on getting a stacktrace.
pg v7.3.2. SuSE 7.3
elein
On Thu, Jul 31, 2003 at 03:56:33AM -0700, Stephan Szabo wrote:
>
> On Thu, 31 Jul 2003, Ang Chin Han wrote:
>
> > In Postgresql 7.3, a datatype can be int, text, etc, and also
> > pseudo-types like RECORD, any, etc.
> >
> > These pseudo types are mainly used in CREATE FUNCTIONs, but what if it's
> > used in a CREATE TABLE, esp. when the name of another table is used as a
> > datatype?
> [...]
> >
> > -- Hmmm... What if pseudo-types in CREATE TABLEs?
> > -- *** THIS IS THE WHAT I'M INTERESTED IN ***
> > CREATE TABLE bar(myfoo foo, c int);
> >
> > -- The only way I can think of to insert values into the table bar:
> > SELECT INTO bar SELECT get_foo1(), 1;
>
> You can write casts as well presumably to allow other inputs.
>
> > -- How to retrieve data from bar?
> > SELECT * FROM bar;
> > ERROR: Cannot display a value of type RECORD
> >
> > SELECT myfoo.a FROM bar;
> > ERROR: Attribute "myfoo.a" not found
>
> However the syntax
> select a(myfoo),b(myfoo) from bar
> should work.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
