Thread: [Fwd: Schema Question]

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;

On Thu, Mar 02, 2006 at 03:45:01PM -0500, Oisin Glynn wrote:
> 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.
<snip>
> 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!

Take a look at searh_path in the manual.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461