Re: CREATE TABLE with a column of type {table name} - Mailing list pgsql-general

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
>