Thread

1. two questions

   Jixue Jerry Liu <cisjl@cs.unisa.edu.au> — 1999-01-12T00:24:36Z

   I am having two questions about psql:
   
   1. How to define a relation with an attribute which takes set values?
       Not really sure what you mean....
   
     eg. I would like to define a table like
          create table emp (name  text, tele  Set(string));  -- it does
                  not work
          One tuple may look like 'Jerry', {83023381,83023800}
          I can not use array since an array is not a set.
   
   2. How to populate instances to a table referring another table? An
      example:
       Create table CITIES (name  text, population  float);
       Insert into cities values ('Adelaide', 100.0);
       Create table people(name  text, live_in  CITIES);
       How to insert a tuple into people saying that 'Jerry' lives in
               Adelaide?
       I can not use
             INSERT INTO people('Jerry', 'Adelaide');
             since  live_in is not a string, it is a table named CITIES
       I am trying to use object reference of OR. Thus a reference path
           can be formed.
   
   Jerry Liu, Australia
   
   
   
   
   
   

2. Re: [GENERAL] two questions

   Tim Williams <williams@ugsolutions.com> — 1999-01-12T10:07:02Z

   > I am having two questions about psql:
   > 
   > 1. How to define a relation with an attribute which takes set values?
   >     Not really sure what you mean....
   > 
   >   eg. I would like to define a table like
   >        create table emp (name  text, tele  Set(string));  -- it does
   >                not work
   >        One tuple may look like 'Jerry', {83023381,83023800}
   >        I can not use array since an array is not a set.
   > 
   > 2. How to populate instances to a table referring another table? An
   >    example:
   >     Create table CITIES (name  text, population  float);
   >     Insert into cities values ('Adelaide', 100.0);
   >     Create table people(name  text, live_in  CITIES);
   >     How to insert a tuple into people saying that 'Jerry' lives in
   >             Adelaide?
   >     I can not use
   >           INSERT INTO people('Jerry', 'Adelaide');
   >           since  live_in is not a string, it is a table named CITIES
   >     I am trying to use object reference of OR. Thus a reference path
   >         can be formed.
   > 
   > Jerry Liu, Australia
   
   The usual approach is to use two tables.
   
   create table cities (name text, population float);
   create table people (name text, city_name text);
   insert into cities values ('Adelaide', 100.0);
   insert into people values ('Jerry', 'Adelaide');
   update cities set population = population + 1 where name = 'Adelaide';
   
   

3. VIEW's on UNION's

   Kaare Rasmussen <kar@webline.dk> — 1999-01-15T07:43:08Z

   I miss <subject>. I know the answer is that I can write them myself,
   but I don't know where to start or end.
   
   See this construct:
   CREATE TABLE gl-amount (
       glam_nr int,
       period date,
       entry_amount numeric(9,0),
       PRIMARY KEY (glam_seq,period)
       );
   
   CREATE VIEW glam_curmth_v AS
       SELECT entry_amount AS amount1 FROM gl-amount
       WHERE date_part(year', period) = date_part('year', now::datetime)
       AND date_part('month', period) = 1
       UNION SELECT entry_amount AS amount2 FROM gl-amount
       WHERE type = 'S' AND  period = 2;  
   
      SELECT entry_amount AS amount1 FROM gl-amount
       WHERE date_part('year', period) = date_part('year', 'now'::datetime)
       AND date_part('month', period) = 1
       UNION SELECT entry_amount AS amount2 FROM gl-amount
       WHERE date_part('year', period) = date_part('year', 'now'::datetime)
       AND date_part('month', period) = 2
       ...
   
   Can it be done without the use of VIEW's on UNION's?