Re: composite type use in pl/gpsql

On Wed, Jun 13, 2012 at 12:25 PM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:
> 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.

couple of things going on here:

first, the way to do insert from composite type is like this:

insert into foo select (f).*;

if f is type of foo.  The actual error you're getting is probably
confusion between variable names and literal objects.  Especially in
older postgres try not to have variable names that happen to be the
same name as tables or columns. I can't tell for sure since the the
whole function isn't posted.

merlin