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(dot)marlowe(at)gmail(dot)com> wrote:
>> On Wed, Feb 1, 2012 at 3:29 PM, Christian Ramseyer <rc(at)networkz(dot)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