Обсуждение: CREATE TABLE with a column of type {table name}

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?

e.g.

-- Just for illustration:
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';

-- Cool uses:
SELECT b FROM get_foo1();
Output:
  b
---
  2
(1 row)


-- 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;

-- 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


Should the CREATE TABLE bar(...) return an error in the first place? How
do we retrieve or somehow make use of bar once we get data into it?
Purely academical question at the moment, but might be an interesting
feature to explore esp. for perspective of OOP.

pg_dump (7.3.2) returns an error when trying to dump this table.

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
   6:00pm  up 217 days,  9:05,  5 users,  load average: 5.26, 5.10, 5.03

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.

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
>

elein <elein@varlena.com> writes:
> This is what I did:
> CREATE TABLE foo(a int, b int);
> CREATE TABLE bar(myfoo foo, c int);

This does not actually work, never has worked (at least not as you're
expecting), and is unlikely to start working in the near future.

The only reason it's not rejected out of hand is that there are still
traces in the code of an old Postquel feature that had this as its API.
It did not work anywhere near the way you think, though.  As best I
understood it, what would actually be stored in a row of table bar would
be the OID of a function declared as "function() returns setof foo", and
there was some syntax or other for calling this function and getting
back the potentially-many rows it would return.  I believe that in the
Postquel system there was actually syntactic sugar for creating nameless
functions of this kind given a Postquel query string that would return
the correct rowtype; so you could imagine the contents of such a field
as being a query that would be executed on-demand and return a table.
Possibly different tables each time you tried it, too.

None of the syntactic sugar is there anymore, and I doubt that the
underlying ability to call a function whose OID appears in a field
works anymore either, even though (some of?) the code for it is still
around.  No one's gotten round to ripping it out entirely, but we
probably should, if it's possible to cause core dumps by creating tables
with complex types as columns.  For sure I have no interest in trying to
resurrect the Postquel behavior.

            regards, tom lane

On Fri, 1 Aug 2003, elein wrote:

> 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.

Yeah, I couldn't make it fail with the below, but I
could make it crash with a little more work. :(
Now to generate a core file...

elein <elein@varlena.com> writes: 

This is what I did:
CREATE TABLE foo(a int, b int);
CREATE TABLE bar(myfoo foo, c int);   

This does not actually work, never has worked (at least not as you're
expecting), and is unlikely to start working in the near future.

The only reason it's not rejected out of hand is that there are still
traces in the code of an old Postquel feature that had this as its API.
It did not work anywhere near the way you think, though.  As best I
understood it, what would actually be stored in a row of table bar would
be the OID of a function declared as "function() returns setof foo", and
there was some syntax or other for calling this function and getting
back the potentially-many rows it would return.  I believe that in the
Postquel system there was actually syntactic sugar for creating nameless
functions of this kind given a Postquel query string that would return
the correct rowtype; so you could imagine the contents of such a field
as being a query that would be executed on-demand and return a table.
Possibly different tables each time you tried it, too.

None of the syntactic sugar is there anymore, and I doubt that the
underlying ability to call a function whose OID appears in a field
works anymore either, even though (some of?) the code for it is still
around.  No one's gotten round to ripping it out entirely, but we
probably should, if it's possible to cause core dumps by creating tables
with complex types as columns.  For sure I have no interest in trying to
resurrect the Postquel behavior.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html

I know that this isn't really supposed to work in postgresql.
I was testing a response stephan made to ang chin han's question.

However, the ability to create virtual columns (you brought it
up :-) was quite handy and survived the conversion to SQL
in Illustra (as did row types).  It was not necessarily
the trick Illustra used to implement row/composite types. I'm
not sure what we did in there.

IMNSHO a type is a type is a type.  An instance of anytype
can go in a column.  However, having worked in some of
the code to make this recursive definition work correctly
I understand your "No! Never!"  response.  I don't agree,
with it, but I understand it :-)

elein

On Fri, Aug 01, 2003 at 06:00:41PM -0400, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > This is what I did:
> > CREATE TABLE foo(a int, b int);
> > CREATE TABLE bar(myfoo foo, c int);
>
> This does not actually work, never has worked (at least not as you're
> expecting), and is unlikely to start working in the near future.
>
> The only reason it's not rejected out of hand is that there are still
> traces in the code of an old Postquel feature that had this as its API.
> It did not work anywhere near the way you think, though.  As best I
> understood it, what would actually be stored in a row of table bar would
> be the OID of a function declared as "function() returns setof foo", and
> there was some syntax or other for calling this function and getting
> back the potentially-many rows it would return.  I believe that in the
> Postquel system there was actually syntactic sugar for creating nameless
> functions of this kind given a Postquel query string that would return
> the correct rowtype; so you could imagine the contents of such a field
> as being a query that would be executed on-demand and return a table.
> Possibly different tables each time you tried it, too.
>
> None of the syntactic sugar is there anymore, and I doubt that the
> underlying ability to call a function whose OID appears in a field
> works anymore either, even though (some of?) the code for it is still
> around.  No one's gotten round to ripping it out entirely, but we
> probably should, if it's possible to cause core dumps by creating tables
> with complex types as columns.  For sure I have no interest in trying to
> resurrect the Postquel behavior.
>
>             regards, tom lane
>

elein <elein@varlena.com> writes:
> IMNSHO a type is a type is a type.  An instance of anytype
> can go in a column.  However, having worked in some of
> the code to make this recursive definition work correctly
> I understand your "No! Never!"  response.  I don't agree,
> with it, but I understand it :-)

I didn't say "No! Never!".  I said this isn't a matter of fixing
a small bug.  For starters, the apparent support for the feature
needs to be ripped out, because it has nothing in common with actual
support.

            regards, tom lane