I have been researching for days about the diff. between pltcl and plpgsql language in postgresql to solve my problem.
I have created a trigger function in 'pltcl' language but now I am trying to convert this in 'plpgsql' language. Any idea on how to convert this piece of trigger function code to plpgsql function? Thanks -
CREATE OR REPLACE FUNCTION "public"."audit_log" () RETURNS trigger AS
-
$body$
-
spi_exec "SELECT CURRENT_USER AS tguser"
-
spi_exec "SELECT relname AS tgname FROM pg_class WHERE relfilenode = $TG_relid"
-
-
#skip changes on audit_table
-
if {[string equal -nocase $tgname audit_table]} { return OK }
-
-
#get PK name
-
set pk_name ""
-
spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
-
WHERE c.relname = '$tgname'
-
AND c.oid=i.indrelid
-
AND a.attnum > 0
-
AND a.attrelid = i.indexrelid
-
AND i.indisprimary='t'"
-
-
switch $TG_op {
-
INSERT {
-
set pk_value ""
-
-
#get PK value
-
foreach field $TG_relatts {
-
if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
-
set pk_value [lindex [array get NEW $field] 1]
-
break;
-
}
-
}
-
#log inserted row values
-
foreach field $TG_relatts {
-
if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
-
set modified_field [lindex [array get NEW $field] 0]
-
-
if {[string compare $modified_field ""] != 0} {
-
-
set current_value [lindex [array get NEW $field] 1]
-
spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
-
VALUES (CURRENT_TIMESTAMP, '[ quote $tguser ]', '[ quote $tgname ]', '[ quote $modified_field ]', '[ quote $pk_name ]', '[ quote $pk_value ]', '$TG_op', NULL, '[ quote $current_value ]')"
-
}
-
}
-
}
-
}
-
UPDATE {
-
set pk_value ""
-
-
#get PK value
-
foreach field $TG_relatts {
-
if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
-
set pk_value [lindex [array get NEW $field] 1]
-
break;
-
}
-
}
-
#log inserted row values
-
foreach field $TG_relatts {
-
#check changed fields
-
if {[string equal -nocase [array get NEW $field] [array get OLD $field]] == 0} {
-
set modified_field [lindex [array get OLD $field] 0]
-
if {[string compare $modified_field ""] == 0} {
-
set modified_field [lindex [array get NEW $field] 0]
-
}
-
set previous_value [lindex [array get OLD $field] 1]
-
set current_value [lindex [array get NEW $field] 1]
-
spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
-
VALUES (CURRENT_TIMESTAMP, '[ quote $tguser ]', '[ quote $tgname ]', '[ quote $modified_field ]', '[ quote $pk_name ]', '[ quote $pk_value ]', '$TG_op', '[ quote $previous_value ]', '[ quote $current_value ]')"
-
}
-
}
-
}
-
DELETE {
-
set pk_value ""
-
-
#get PK value
-
foreach field $TG_relatts {
-
if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
-
set pk_value [lindex [array get OLD $field] 1]
-
break;
-
}
-
}
-
#log inserted row values
-
foreach field $TG_relatts {
-
if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
-
set modified_field [lindex [array get OLD $field] 0]
-
set previous_value [lindex [array get OLD $field] 1]
-
spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
-
VALUES (CURRENT_TIMESTAMP, '[ quote $tguser ]', '[ quote $tgname ]', '[ quote $modified_field ]', '[ quote $pk_name ]', '[ quote $pk_value ]', '$TG_op', '[ quote $previous_value ]', NULL)"
-
}
-
}
-
}
-
}
-
return OK
-
$body$
-
LANGUAGE 'pltcl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
0 2373 Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rodusa |
last post by:
I am having problem to apply updates into this function below. I tried
using cursor for updates, etc. but no success. Sql server keeps telling
me that I cannot execute insert or update from inside...
|
by: Julie May |
last post by:
I have 90% of my function working and I know to get the next 10% it is justa matter of getting the quotations and the escaping quotations correct. Here is the portion that does work:
<working...
|
by: lnd |
last post by:
After copied pg database from one PC to another
-I could not find plpgsql function(s) in the copied database.
-had to instal plpgsql language handler again
-whilst tables and data moved...
|
by: Karl O. Pinc |
last post by:
I'd like to write:
SELECT larger(colA, colB) FROM foo
and am wondering the best way to go about it.
(Really, I'd like the larger() function to take an arbitrary
number of arguments but I...
|
by: Ed |
last post by:
I want to convert the following VB code to C#:
Dim r as datarow
Dim i as integer
i = fix(r("NumberField"))
Fix as you may know truncates a floating point number to the right of the
decimal...
|
by: jeremito |
last post by:
I am extending python with C++ and need some help. I would like to
convert a string to a mathematical function and then make this a C++
function. My C++ code would then refer to this function to...
|
by: setheo |
last post by:
To all gurus,
I am currently converting some of C++ codes to VB.net
The C++ Codes is as follows :
================= C++ CODE ==================
typedef struct _tagBBCameraParameter
{...
|
by: arti |
last post by:
I dont want to use Convert(Char(9),date,106) function to show date in dd/MM/yyyy format. It changes the datatype of my column to char & I cant perform other date operations on it without changing it...
|
by: Emil |
last post by:
Is it posiible to create callback for a function, that is automatically
called before that function returns? I need it for tracing/profiling
purposes.
eg.:
var execTime = null;
var startTime...
|
by: Ellen P |
last post by:
Hi there,
I've been trying to execute \COPY from within a plpsql function and keep getting an error. Any hints at where I'm going wrong would be great.
I'm using \COPY with a backslash...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
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...
|
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...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
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...
|
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...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |