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 2380 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: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |