Re: Trigger on Postgres for tables syncronization

From: Jeff Davis <jdavis-pgsql(at)empires(dot)org>
To: Prabu Subroto <prabu_subroto(at)yahoo(dot)com>
Cc: Postgres General Milis <pgsql-general(at)postgresql(dot)org>
Subject: Re: Trigger on Postgres for tables syncronization
Date: 2004-07-27 17:12:13
Message-ID: 1090948333.4431.7.camel@jeff
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Try a view defined like:

CREATE VIEW appointment0 AS SELECT * FROM appointment WHERE done='Y';
CREATE VIEW appointment1 AS SELECT * FROM appointment WHERE done='N';

Then appointment0 and appointment1 are not real tables, but "virtual
tables". You can still do:

SELECT * FROM appointment0;
or
SELECT * FROM appointment1;

Now if you insert a record into appointment or update a record in
appointment it will automatically appear in the output of appointment0
or appointment1 depending on the value of "done". You never have to
insert into appointment0 or appointment1.

Regards,
Jeff Davis

On Tue, 2004-07-27 at 04:58, Prabu Subroto wrote:
> Dear my friends...
>
> I am using SuSE Linux 9.1 and postgres. I am a
> beginner in postgres, usually I use MySQL.
>
> I have 3 tables : appointment, appointment0 and
> appointment1.
>
> the fields of table "appointment" are as follows:
> noapp* (int4):ID Number of appointment (PK)
> custid (int4) : Customer ID
> salesid (int4) : Sales ID
> date (date) : Date of appointment
> time (time) : Time of appointment
> todo (char(150)) : What's to do with them
> done (char(1)): whether done (N/Y)
> warned (char(1)): whether warned with prompt
> timestamp (timestamp) : timestamp of record
>
> "appointment0" and "appointment1" have exactly the
> same field names as what "appointment" has.
>
> But...
> 1. the population of "appointment0" and "appointment1"
> are the subset of "appointment"
> 2. what the "appointment0" has are the members of
> "appointment" whose "Y" as the value of fieldname
> "done".
> 3. and what "appointmnet1" has are the members of
> "appointment" whose "N" as the value of fieldname
> "done".
>
> I want if my program inserted, updated, deleted the
> record of "appointment" than the postgres does the
> syncronization to the corresponded tables
> (appointment0 or appointment1 or both).
>
> Is it possible to implement this strategy with
> trigger? But how?
>
> Where Can I find a good documentation about the
> trigger of postgres especially the PLPGSQL of the
> postgres?
>
> Anybody would be so nice to tell me the steps and the
> command of the triggers should be in order to
> implement my strategy? Please....
>
> Please....
>
> Thank you very much in advance.
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Prabu Subroto 2004-07-27 17:16:11 altering a table to set serial function
Previous Message Stephan Szabo 2004-07-27 15:14:44 Re: Trigger on Postgres for tables syncronization