Hi,
I have some tables and functions in the public schema. This was
probably not the right thing to do.
I have a seperate schema with a function and table. The function does a
select on the table.
The table and function exist in public schema and betteridea schema. I
have a user betteridea who owns the betteridea schema.
The table in public schema has identical structure but different data to
the table in betteridea schema.
table employee_list
function zfunc_get_employee
If I connect as the betteridea user and do
select * from zfunc_get_employee('1234');
or
select * from betteridea.zfunc_get_employee('1234');
I get the correct result the result based on the records in the
betteridea.employee_list table in both cases.
If I connect as postgres user and do select * from
betteridea.zfunc_get_employee('1234');
or
select * from zfunc_get_employee('1234');
with no schema in front of it.
It is returning the data from the public.employee_list in both cases!
I would have expected that the function in the betteridea schema would
access the table in the betteridea schema without having to specifically
prefix the table name with the schema name.
My whole plan here is to have a schema per client and use the schemas to
segregate the data. I would like not to have to change the functions to
prefix table names with schema names.
The betteridea.zfunc_get_employee function below.
Any thoughts?
Oisin
CREATE OR REPLACE FUNCTION newschema.zfunc_get_employee( int4) RETURNS
"varchar" AS
$BODY$DECLARE
v_extn integer;
v_employee varchar;
BEGIN
v_extn:=$1;
select employee into v_employee from employee_list where extension =
v_extn;
return v_employee;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;