I think I got it:
CREATE FUNCTION new_get_emp_id() RETURNS INTEGER AS $$ select emp_id
from secureview.tbl_employee where username = (SELECT current_user) $$
LANGUAGE SQL IMMUTABLE;
I made the function immutable so it only calls it once, therefore no
longer requiring a call per-row.
John McCawley wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
