Hello, Thanks in advance for taking my question. Running on Greenplum 4.1.2/ based on PG 8.2.3 kernal We make extensive use of functions to do our ETL. So, I’m building a stored procedure template for our developers. I’d like the template to log the sql statements to a logging table for audit/debug purposes. Statements are logged after execution so we have a completion code. I’d also like to have an exception block for each statement – so prior steps get committed. Since out procedures can have a large number of steps <20, I’d like to avoid repeating the exception and logging code. In PL/pgsql there doesn’t seem to be a subroutine/goto concept, so I am trying to make the statements as concise as possible utilize composite types for the log table -- template -- header -- blah -- change log -- blah -- declare Logsp type_log_site_process%ROWTYPE; BEGIN -- function setup logsp.proc_id :=0; logsp.proc_name :=vSpName; logsp.step_id :=1; logsp.step_desc :=''; Logsp.step_starttime := clock_timestamp(); Logsp.step_endtime := clock_timestamp(); logsp.step_returncode :=''; logsp.activity_count :=1; logsp.status_desc :=''; logsp.status_flag :='P'; -- step block -- setup Starttime = clock_timestamp(); -- execute sql -- exception block End time = clock_timestamp(); -- log statement Instead of INSERT INTO log_site_process( id, proc_id, proc_name, step_id, step_desc, step_starttime, step_endtime, step_returncode, activity_count, status_flag, status_desc) VALUES (logsp.proc_id , logsp.proc_name , logsp.step_id , logsp.step_desc , Logsp.step_starttime , Logsp.step_endtime , logsp.step_returncode , logsp.activity_count , logsp.status_desc , logsp.status_flag ; I’d like insert into log_site_process select * from (Logsp); -- or values (logsp) but I can’t seem to get it to work. ERROR: syntax error at or near "$1" LINE 1: ...ert into oww_mart_tbls.log_site_process select * from ( $1 ) ^ Can someone let me know if I can do this and what the syntax is. Thanks Doug Doug Little |
