Re: Audit-trail engine: getting the application's layer user_id

* Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx> [20070425 17:57]:
> I think you can use a plpgsql function with execute. For instance, if
> the name of your temp table is current_user_id the function will be
> something like:
>
> create function get_current_user_id() returns int as $$
> declare
> v_rec record;
> v_user int;
> v_query text;
> begin
> v_query := 'SELECT user_id FROM current_user_id';
> for v_rec in execute v_query loop
> v_user := v_rec.user_id;
> end loop;
> return v_user;
> end;
> $$ language plpgsql;
>
> Untested but that's the idea, you need to use execute to avoid the
> caching of the plan. You might also want to control what happens when
> the table does not exist and that can be done handling the
> corresponding exception. Check the docs for the details.

Excellent, that works exactly as needed! I will rely on the permanent
table being there always to provide the default value if no temporary
table has been created.

Thanks! Til