CREATE TABLE & composite type

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&gt;
Sent from the PostgreSQL - general mailing list archive
<http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html&gt; at
Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general