|
P: 1
|
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;
| |
