On Wed, Jun 28, 2017 at 8:37 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
