Re: Audtiting, DDL and DML in same SQL Function

On Wed, Feb 1, 2012 at 3:29 PM, Christian Ramseyer <rc(at)networkz(dot)ch> wrote:
> Hello list
>
> I'm trying to build a little trigger-based auditing for various web
> applications. They have many users in the application layer, but they
> all use the same Postgres DB and DB user.
>
> So I need some kind of session storage to save this application level
> username for usage in my triggers, which AFAIK doesn't exist in
> Postgres. Googling suggested to use a temporary table to achieve
> something similar.
>
> Question 1: Is this really the right approach to implement this, or are
> there other solutions, e.g. setting application_name to user(at)application
> and using this in the triggers or similar workarounds?
>
> On to question 2:
>
> So now I was trying this:
>
> create or replace function audit_init(text, text) returns void as $$
>
>    create temporary table application_session (
>        "user" text,
>        "application" text
>    ) with ( oids = false);
>
>   insert into application_session
>      ( "user", "application")  values ($1, $2);
>
> $$
> language sql volatile;
>
> Which unfortunately can't be created or executed, as it says:
>
> ERROR:  relation "application_session" does not exist
> LINE 8:     insert into application_session ("user", "application") ...
>
> When I manually create the temporary table first, I can create the
> function, but then when launching it in a new session that doesn't have
> the table yet the error is the same.
>
> If I split it up in two functions, one with the insert and one with the
> create, it works fine. So apparently the objects in the DML must be
> available at parse time of the function body. Is there an easy way
> around this? 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.