Re: Audtiting, DDL and DML in same SQL Function

On 2/2/12 12:39 AM, Scott Marlowe wrote:
> On Wed, Feb 1, 2012 at 4:27 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Wed, Feb 1, 2012 at 3:29 PM, Christian Ramseyer <rc@networkz.ch> wrote:
>>> Optimally, I'd just have my applications perform a single
>>> call after connecting, e.g. "audit_init('USERNAME', 'Name of application')".
>>
>> I think if you build the query as a string and EXECUTE it it will
>> work.  But I'm not guaranteeing it.
>
> Note that you might have to build both queries and EXECUTE them to make it work.
>

Thanks Scott, executing it actually does the trick. I'm now using this:

create or replace function audit_start(text, text) returns void as $$
    declare
            username alias for $1;
            application alias for $2;
    begin

        execute 'drop table if exists audit_session ;
                create temporary table audit_session (
            username text, application text)';

        execute 'insert into audit_session
                          (username, application)
              values ($1, $2)'
            using username, application;
    end;
$$
language plpgsql;

Christian