Hi,
My boss wants to add a special type of logging to some of our tables on
update/delete/insert. I need to log who, when, table_name, field name,
original value and new value for each record, but only logging modified
fields, and he wants me to do this using postgres pgSQL triggers. The
changes would be inserted into a second table.
We are given 10 automatically created variables. Some of which I know I can
use: NEW, OLD, TG_WHEN, TG_OP and TG_RELNAME. I can use these to get
general information for the update, but when the trigger is called, I don't
know how many fields are in the tables that are being updated.
My questions are: Is there a way I can dynamically determine the number of
fields in the rows that is being maintained. (a function much like:
PQnfields(const PGresult *); )
Then I need a way to get the name of the field (using a function much like:
PQfname(const PGresult *, int); )
Using the dynamically generated name I could then walk the NEW and OLD
columns to compare the values. (e.g. if (NEW.field != OLD.field) do
something;);
Can anyone help me with this? Thank you in advance.
Tim Vadnais
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
2  4665 
"Tim Vadnais" <tv******@earth link.net> writes: My questions are: Is there a way I can dynamically determine the number of
fields in the rows that is being maintained.
I'm starting to think there should be a FAQ entry for this ;-)
plpgsql is essentially incapable of doing anything that involves dynamic
field access, especially if the field types aren't known in advance either.
I believe you can do what you want in pltcl, and you can definitely
write such a trigger in C, but plpgsql is the wrong tool for the job.
If you want to try it in C, there are some relevant examples in
contrib/spi/ in the PG source distribution.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jared Evans |
last post by:
I have a feeling I'll be forced to use a script and a trigger for this
type of field format but I'm wondering if any of your wizards could
point at a simple way I could do something like this:
For example, if I want to be able to keep track of new orders following
this incrementing convention:
ORD100000001
ORD100000002
ORD100000003
|
by: Josué Maldonado |
last post by:
Hello list,
First of all, excuse me if this is not the right place to ask my question.
Is there a way in postgresql to loop to all the fields of a given table
and compare the OLD and NEW value for each field. I need to make an
audit table that must contain only the fields changed after and
insert/update.
Thanks in advance
|
by: D. Stimits |
last post by:
I've found a number of basic references for PL/PGSQL, but am looking for
something more complete. First question, is there available a *complete*
reference for PL/PGSQL? I'm using PostgreSQL version 7.2.3-5.73 (for
Redhat rpm packaging version), so it is version 7.2 I am concerned with
information on (this is a default version, so at least on this machine,
I'm not interested in updates in version).
The situation that leads me to ask for...
|
by: Poul Møller Hansen |
last post by:
Hi,
I need an auto incrementing field that will contain values like
N000001, N000002, N000003 etc.
I think the way is to use the value from an identity field in a stored
procedure that is triggered at insert.
I can't see that it can be made in pure SQL, but Java is not a problem.
Any of you that can tell me the way of doing it ?
|
by: Jim Bowe |
last post by:
I converted to a DB2 database where the data type was changed from
autonumber to number field. I still need to increment the field by one
each time a new record is added. Can anyone help me?
Thanks
| | |
by: Bob |
last post by:
Hi,
I have a very odd postgresql problem.
I have some subqueries contained within a function which are looking
for unrefernced data and then delete any rows that are found:
CREATE OR REPLACE FUNCTION housekeeping()
RETURNS TRIGGER AS '
BEGIN
|
by: Justin Clift |
last post by:
Hi all,
I'm creating a centralised table to keep a log of changes in other tables.
In thinking about the PL/pgSQL trigger to write and attach to the
monitored tables (probably a row level AFTER trigger), I can see two
approaches:
a) Write a separate PL/pgSQL function for each table, with the hard
coded field names in the function.
|
by: Graeme Hinchliffe |
last post by:
Hiya,
Not had much experience with tiggers under postgres but am liking them so
far.
My problem is this. I am writing an updates system, postgres holds the
master copy of the database, any changes made to this are logged in an
updates table which is monitored by a daemon, which if any updates are
spotted propigates them to the remote servers.
I have written an initial trigger that spots any changes made to the
|
by: Tim Vadnais |
last post by:
Hi,
My boss wants to add some logging functionality to some of our tables on
update/delete/insert. I need to log who, when, table_name, field name,
original value and new value for each record, but only logging modified
fields, and he wants me to do this wing postgres pgSQL triggers.
We are given 10 automatically created variables. Some of which I know I can
use: NEW, OLD, TG_WHEN, TG_OP and TG_RELNAME. I can use these to get...
|
by: pravinasp |
last post by:
Hello there
I have been trying to generate a dynamic querystring thats to be executed inside a trigger function and every time I use NEW.* pl/pgsql throws an error saying 'column * does not exist'.
CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
RETURNS trigger AS
$$
BEGIN
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
