CREATE TABLE with a column of type {table name}

From: Ang Chin Han <angch(at)bytecraft(dot)com(dot)my>
To: pgsql-general(at)postgresql(dot)org
Subject: CREATE TABLE with a column of type {table name}
Date: 2003-07-31 10:42:41
Message-ID: 3F28F2A1.6040203@bytecraft.com.my
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonathan Mezach 2003-07-31 10:46:08 Re: Updating from update trigger
Previous Message David W Noon 2003-07-31 10:19:25 Re: OT: Address Fields