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
Expand|Select|Wrap|Line Numbers
- 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;