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@postgresql.org so that your
> message can get through to the mailing list cleanly