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