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

[Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx>]

Tilmann Singer <tils-pgsql(at)tils(dot)net> writes:

> Can I define a view which references a table in a way so that it will
> use a temporary table of the same name if it exists, otherwise the
> permanent table with that name?

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.

Regards,
Manuel.