On Wed, Jun 28, 2017 at 8:37 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 06/28/2017 06:27 AM, gmb wrote:
>>
>> Hi Referencing https://www.postgresql.org/docs/9.6/static/rowtypes.html
>> Taking a chance here.... Is there a short-hand way in which I can create a
>> table with the same structure as a user defined composite type ? E.g. CREATE
>> TYPE inventory_item AS ( name text, supplier_id integer, price numeric );
>> CREATE TABLE inventory_item_table ( like type inventory_item ); We're using
>> composite types rather extensively as the return structure of functions:
>> CREATE FUNCTION some_func() RETURNS SETOF inventory_item ....; Of course I
>> can: CREATE TABLE inventory_item_table AS ( SELECT some_func( ) );
>
> CREATE TABLE inventory_item_table AS ( SELECT some_func( ) limit 0);
I think it's better to use the (somewhat arcane but designed for this
exact purpose) 'OF' syntax (hat tip to Peter E). This is particularly
useful if you want to have multiple tables mirror the composite type
and manage the definition through the rowtype:
postgres=# create type foo as (a int, b int);
CREATE TYPE
postgres=# create table bar of foo;
CREATE TABLE
Time: 0.973 ms
postgres=# \d bar
Table "public.bar"
Column │ Type │ Modifiers
────────┼─────────┼───────────
a │ integer │
b │ integer │
Typed table of type: foo
\h CREATE TABLE
<snip>
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [
IF NOT EXISTS ] table_name
OF type_name [ (
<snip>
postgres=# alter type foo add attribute c text cascade;
ALTER TYPE
postgres=# \d bar
Table "public.bar"
Column │ Type │ Modifiers
────────┼─────────┼───────────
a │ integer │
b │ integer │
c │ text │
Typed table of type: foo
merlin