CREATE TABLE with a column of type {table name}

Поиск
Список
Период
Сортировка
От Ang Chin Han
Тема CREATE TABLE with a column of type {table name}
Дата
Msg-id 3F28F2A1.6040203@bytecraft.com.my
обсуждение исходный текст
Ответы Re: CREATE TABLE with a column of type {table name}  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-general
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

Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Govorun
Дата:
Сообщение: Re: Don't removes/recycles WAL files at all
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: CREATE TABLE with a column of type {table name}