Re: Any form of connection-level "session variable" ?

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