composite type use in pl/gpsql

From: "Little, Douglas" <DOUGLAS(dot)LITTLE(at)orbitz(dot)com>
To: "PostgreSQL General (pgsql-general(at)postgresql(dot)org)" <pgsql-general(at)postgresql(dot)org>
Subject: composite type use in pl/gpsql
Date: 2012-06-13 17:25:31
Message-ID: 8585BA53443004458E0BAA6134C5A7FBAD028F82@EGEXCMB01.oww.root.lcl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2012-06-13 17:41:42 Re: Create view is not accepting the parameter in postgres functions
Previous Message Tom Lane 2012-06-13 17:25:27 Re: Create view is not accepting the parameter in postgres functions