Thread: how to add array of objects to a record
Hi list, Can I add an array of object to a record? For example if I have a class (or type) phone_number: create type phone_number as( name char(20), caption char(50), ph_num char(25)); and I would like associate several (unknown number, a priori) phone numbers to a record "persons" can I create a table like this: create table persons( id integer, first_name char(50), family_name char(50), phone_numbers phone_number[]) and how I have to write the insert and the select queries to put and get the array of phone numbers? thank you Domenico
>-----Original Message----- >From: pgsql-general-owner@postgresql.org >[mailto:pgsql-general-owner@postgresql.org] On Behalf Of dfx >Sent: Sunday, 3 February 2008 10:38 >To: pgsql-general@postgresql.org >Subject: [GENERAL] how to add array of objects to a record > >Hi list, > >Can I add an array of object to a record? > >For example if I have a class (or type) phone_number: > >create type phone_number as( >name char(20), >caption char(50), >ph_num char(25)); > >and I would like associate several (unknown number, a priori) >phone numbers to a record "persons" >can I create a table like this: > >create table persons( >id integer, >first_name char(50), >family_name char(50), >phone_numbers phone_number[]) > >and how I have to write the insert and the select queries to >put and get the array of phone numbers? Its an SQL database, do not fall back to paradigms from imperative programming languages (like C, Java, PHP, Perl, ...). Postgres is an implementation of the relational model, which works very nice on sets. It does not have a good concept of pointers and alike (they can be represented, but inefficiently). Transform you type into a table and add an "id" attribute that references the persons table (using a foreign key). At this point you can use regular SQL statements. - Joris
Yes, this is the "normal" way, but I was tempted to investigate the
possibility
to use array (of string) or composite types to avoid to increase the number
of tables
and to simplify stored procedures reducing the number of join.
Thi idea was born following the discussion concerning EAV.
Thank you for contribute.
Domenico
-----Messaggio originale-----
Da: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Per conto di Joris
Dobbelsteen
Inviato: domenica 3 febbraio 2008 12:16
A: dfx@dfx.it; pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] how to add array of objects to a record
>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of dfx
>Sent: Sunday, 3 February 2008 10:38
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] how to add array of objects to a record
>
>Hi list,
>
>Can I add an array of object to a record?
>
>For example if I have a class (or type) phone_number:
>
>create type phone_number as(
>name char(20),
>caption char(50),
>ph_num char(25));
>
>and I would like associate several (unknown number, a priori)
>phone numbers to a record "persons"
>can I create a table like this:
>
>create table persons(
>id integer,
>first_name char(50),
>family_name char(50),
>phone_numbers phone_number[])
>
>and how I have to write the insert and the select queries to
>put and get the array of phone numbers?
Its an SQL database, do not fall back to paradigms from imperative
programming languages (like C, Java, PHP, Perl, ...). Postgres is an
implementation of the relational model, which works very nice on sets.
It does not have a good concept of pointers and alike (they can be
represented, but inefficiently).
Transform you type into a table and add an "id" attribute that
references the persons table (using a foreign key). At this point you
can use regular SQL statements.
- Joris
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Sun, Feb 03, 2008 at 10:00:04PM +0100, dfx wrote: > Yes, this is the "normal" way, but I was tempted to investigate the > possibility to use array (of string) or composite types to avoid to > increase the number of tables That is an extremely bad thing to "optimize" for. Add tables as needed for your data. > and to simplify stored procedures reducing the number of join. That's a bad thing to "optimize" for, too. Just do your JOINs, and *if* you discover a performance problem, come back here and get help on it. > Thi idea was born following the discussion concerning EAV. EAV is just a mistake. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate