Thread: [GENERAL] CREATE TABLE & composite type
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( ) ); But , in some cases , where the function returns a large number of records I want to create the table beforehand in order to add indexes: CREATE TABLE inventory_item_table ( like type inventory_item ); CREATE INDEX idx ON inventory_item_table ( id ); INSERT INTO inventory_item_table SELECT some_func(); Will appreciate any input. Regards gmbouwer
View this message in context: CREATE TABLE & composite type
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
View this message in context: CREATE TABLE & composite type
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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); But , in some cases , where the function > returns a large number of records I want to create the table beforehand > in order to add indexes: CREATE TABLE inventory_item_table ( like type > inventory_item ); CREATE INDEX idx ON inventory_item_table ( id ); > INSERT INTO inventory_item_table SELECT some_func(); Will appreciate any > input. Regards gmbouwer > ------------------------------------------------------------------------ > View this message in context: CREATE TABLE & composite type > <http://www.postgresql-archive.org/CREATE-TABLE-composite-type-tp5969032.html> > Sent from the PostgreSQL - general mailing list archive > <http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at > Nabble.com. -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote
>
> CREATE TABLE inventory_item_table AS ( SELECT some_func( ) limit 0);
Thanks Adrian
My concern is that this will have to execute all code in the function only
to return a empty dataset - his may be ineffective.
A possible workaround will be to have a dedicated param which will determine
whether or not to exit at the start of the function.
I also just realised that a possible workaround will be to create tables
instead of composite types ("Whenever you create a table, a composite type
is also automatically created").
I.e. instead of doing:
CREATE TYPE inventory_item AS ( name text, supplier_id integer, price
numeric );
create an actual table (which will never be used):
CREATE TABLE inventory_item AS ( name text, supplier_id integer, price
numeric );
However, having unused tables in the schema kind of goes against the
grain...
Regards
--
View this message in context: http://www.postgresql-archive.org/CREATE-TABLE-composite-type-tp5969032p5969045.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
On 06/28/2017 06:52 AM, Merlin Moncure wrote: > 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: >>> >> 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: Wow, did not know that existed. Thanks. -- Adrian Klaver adrian.klaver@aklaver.com
Merlin Moncure-2 wrote > postgres=# create type foo as (a int, b int); > CREATE TYPE > postgres=# create table bar of foo; > CREATE TABLE > Time: 0.973 ms Exactly what I needed, Thanks a lot, Merlin -- View this message in context: http://www.postgresql-archive.org/CREATE-TABLE-composite-type-tp5969032p5969058.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.