473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Trigger or Rule ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi everybody,

I have tables like

create table xx (
id int4,
col1 whatever,
col2 whatever,
col3 whatever,
valid_from date,
valid_thru date);

where the primary key is always (id, valid_from).
Now my software has a routine that checks on update if
there is a currently valid row (valid_from <= current_date < valid_thru) with
a valid_from matching the valid_from < today
If so, the currently valid row is updated with valid_thru = yesterday.
The new record then is inserted (instead of updated) with a valid_from of
today and a valid_thru of some date far in the future ('9999-01-01')

If there is a currently valid record with valid_from = today, then a normal
update is performed.

In the end this produces a record history with a granularity of one day.
Since I perform a LOT of these calls (sometimes about 1000 such "update
queries" at a time) I thought it might be a great idea to push this
checking/updating/inserting into the database, instead of performing that
outside from the application side.

Questions:

a) Which would be better (faster, less expensive): Trigger or Rule ?
b) If Trigger, how can I avoid that the trigger triggers itself (in the end it
performs an update to the same table)
c) How can I avoid to create explicit updates/inserts for this, i.e. I don't
want to to a "insert into xx (id,col1,col2,col3,valid_from,valid_thru) values
(new.id,new.col1,new.col2 .........", instead I'd like to create a function
that gets a tablename and rowset with the new data and creates the insert
statement itself. Then it could be applied to any table matching the above
schema.

My bet would be a trigger, since it calls a function directly, however then I
obviously can't control the parameters given to the function.

Any help is greatly appreciated.

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/odp2jqGXBvRToM4RAk9RAJ9qdjG+0h4EVjIDGyiNuFqKahZXqg CbBLJM
fs79gvQUU+jq+vZ9VJRF5M8=
=xfBy
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
0 1136

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is...
2
by: Jules Alberts | last post by:
Hello everyone, Several columns in sereval tables in my DB should always be lowercase. I now have a simple function: create or replace function code_lower() returns trigger as ' begin...
2
by: Agnes | last post by:
I got two same tables (invoice, invoice_history) When user insert the record, (i will save a copy in the table invoice_history) when user amend the record, (i iwll save the updated record in the...
3
by: Barbara Lindsey | last post by:
I have a case where I am collecting a "Start Date" and an "End Date". I would like to default the "End Date" to the "Start Date" value if only the "Start Date" is entered. I tried setting this as...
1
by: Bastian Blank | last post by:
I'm using postgresql 7.4.2 and COPY FROM don't trigger INSERT rules. Definitions: | CREATE TABLE log.package_status ( | version integer NOT NULL, | architecture integer NOT NULL, | ...
4
by: Naeem Bari | last post by:
Hi, I am using postgres 7.4.5 on Redhat Enterprise Linux 3. My background is really on Oracle, and I am porting a largish database over to postgres. Here is my problem: On oracle, I...
2
by: Dima Gofman | last post by:
I have a trigger on UPDATE on a table. I'm running some maintenance UPDATE and DELETE queries which I want the trigger to ignore but at the same time I want other UPDATE queries that other users...
3
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code...
1
by: DennBen | last post by:
I am doing an update to set a field value = anothe field value (in the same table) where it is not supplied. I'm handling this in the trigger, but am getting deadlocks. Do you see anything...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.