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

[Tilmann Singer <tils-pgsql(at)tils(dot)net>]

* 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