473,390 Members | 1,476 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

Convert a 'pltcl' function to 'plpgsql' function (postgresql)

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

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION "public"."audit_log" () RETURNS trigger AS
  2.     $body$
  3.     spi_exec "SELECT CURRENT_USER AS tguser"
  4.     spi_exec "SELECT relname AS tgname FROM pg_class WHERE relfilenode = $TG_relid"
  5.  
  6.     #skip changes on audit_table
  7.     if {[string equal -nocase $tgname audit_table]} { return OK }
  8.  
  9.     #get PK name
  10.     set pk_name ""
  11.     spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
  12.      WHERE c.relname = '$tgname'
  13.      AND c.oid=i.indrelid
  14.      AND a.attnum > 0
  15.      AND a.attrelid = i.indexrelid
  16.      AND i.indisprimary='t'"
  17.  
  18.     switch $TG_op {
  19.     INSERT {
  20.       set pk_value ""
  21.  
  22.       #get PK value
  23.       foreach field $TG_relatts {
  24.         if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
  25.           set pk_value [lindex [array get NEW $field] 1]
  26.           break;
  27.         }
  28.       }
  29.       #log inserted row values
  30.       foreach field $TG_relatts {
  31.         if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
  32.           set modified_field [lindex [array get NEW $field] 0]
  33.  
  34.          if {[string compare $modified_field ""] != 0} {
  35.  
  36.           set current_value [lindex [array get NEW $field] 1]
  37.           spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
  38.             VALUES (CURRENT_TIMESTAMP, '[ quote $tguser ]', '[ quote $tgname ]', '[ quote $modified_field ]', '[ quote $pk_name ]', '[ quote $pk_value ]', '$TG_op', NULL, '[ quote $current_value ]')"
  39.           }
  40.         }
  41.       }
  42.     }
  43.     UPDATE {
  44.       set pk_value ""
  45.  
  46.       #get PK value
  47.       foreach field $TG_relatts {
  48.         if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
  49.           set pk_value [lindex [array get NEW $field] 1]
  50.           break;
  51.         }
  52.       }
  53.       #log inserted row values
  54.       foreach field $TG_relatts {
  55.         #check changed fields
  56.         if {[string equal -nocase [array get NEW $field] [array get OLD $field]] == 0} {
  57.           set modified_field [lindex [array get OLD $field] 0]
  58.           if {[string compare $modified_field ""] == 0} {
  59.             set modified_field [lindex  [array get NEW $field] 0]
  60.           }
  61.           set previous_value [lindex [array get OLD $field] 1]
  62.           set current_value  [lindex [array get NEW $field] 1]
  63.           spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
  64.             VALUES (CURRENT_TIMESTAMP, '[ quote $tguser ]', '[ quote $tgname ]', '[ quote $modified_field ]', '[ quote $pk_name ]', '[ quote $pk_value ]', '$TG_op', '[ quote $previous_value ]', '[ quote $current_value ]')"
  65.         }
  66.       }
  67.     }
  68.     DELETE {
  69.       set pk_value ""
  70.  
  71.       #get PK value
  72.       foreach field $TG_relatts {
  73.         if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
  74.           set pk_value [lindex [array get OLD $field] 1]
  75.           break;
  76.         }
  77.       }
  78.       #log inserted row values
  79.       foreach field $TG_relatts {
  80.         if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
  81.           set modified_field [lindex [array get OLD $field] 0]
  82.           set previous_value [lindex [array get OLD $field] 1]
  83.           spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
  84.             VALUES (CURRENT_TIMESTAMP, '[ quote $tguser ]', '[ quote $tgname ]', '[ quote $modified_field ]', '[ quote $pk_name ]', '[ quote $pk_value ]', '$TG_op', '[ quote $previous_value ]', NULL)"
  85.         }
  86.       }
  87.     }
  88.     }
  89.     return OK
  90.     $body$
  91. LANGUAGE 'pltcl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Jul 16 '15 #1
0 2379

Sign in to post your reply or Sign up for a free account.

Similar topics

4
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...
1
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...
10
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...
2
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...
8
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...
5
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...
0
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 {...
6
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...
2
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...
0
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...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
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...
1
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...
0
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...
0
marktang
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,...
0
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...
0
Oralloy
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,...
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.