Re: [GENERAL] [Fwd: Schema Question]
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.
> 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 [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] [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;
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
