| 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: | Whole Thread | Raw Message | 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
| 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 |