This is revisiting a problem I posed to this group a month or so ago
regarding separating different users' data through schema views. The
solution we're using is based on a suggestion we received here:
http://archives.postgresql.org/pgsql-general/2006-12/msg00037.php
Everything is working great with the exception of performance. One of
our tables has close to a million records, and the overhead of calling
the get_client_id() function per row is eating us alive, I assume
because it is having to per-row call a select on a table to retrieve
the proper ID within the function.
Is there any way I could establish this ID initially in some sort of
connection-level variable, and from this point on reference that
variable? What I'm thinking is something like the following:
select initialize_client_id(); //This would actually hit the DB to
retrieve the proper ID for the logged in user
//Now, in the view get_client_id() retrieves the earlier established
"variable" instead of hitting the DB
select foo,bar FROM tbl_foo WHERE client_id = get_client_id();
Am I incorrect in assuming that the statement:
select foo from tbl_bar WHERE client_id = get_client_id();
will call get_client_id() for every row?
John