david williams wrote:
> Also,
>
> the table definition MUST be in the Public Schema. I use my own schema
> names but in order for the table to be found by the function it ( the
> table ) must be in the public schema. Although it can be empty.
(Note: this discussion does not apply to PostgreSQL releases prior to 7.3 beta)
Not true. You need to be sure the schema the table is in is in your search
path, or you need to fully qualify the table reference. See below for an example:
-- create a new schema
CREATE SCHEMA s1;
CREATE SCHEMA
-- change to the new schema
SET search_path='s1','$user','public';
SET
select current_schema(); current_schema
---------------- s1
(1 row)
-- create the table
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE TABLE
INSERT INTO foo VALUES(1,1,'Joe');
INSERT 794076 1
-- change back to public schema, but leave s1 in the search path
SET search_path='$user','public','s1';
SET
select current_schema(); current_schema
---------------- public
(1 row)
\dt List of relations Schema | Name | Type | Owner
--------+------+-------+---------- s1 | foo | table | postgres
(1 row)
CREATE FUNCTION getfoo(int) RETURNS foo AS ' SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;
CREATE FUNCTION
\df getfoo List of functions Result data type | Schema | Name | Argument data types
------------------+--------+--------+--------------------- foo | public | getfoo | integer
(1 row)
-- this will work
SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper
-------+----------+---------+------- 1 | 1 | Joe | JOE
(1 row)
-- now try again with table name qualified in the function
DROP FUNCTION getfoo(int);
DROP FUNCTION
-- remove s1 from the search path
SET search_path='$user','public';
SET
select current_schema(); current_schema
---------------- public
(1 row)
\dt
No relations found.
CREATE FUNCTION getfoo(int) RETURNS s1.foo AS ' SELECT * FROM s1.foo WHERE fooid = $1;
' LANGUAGE SQL;
CREATE FUNCTION
\df getfoo List of functions Result data type | Schema | Name | Argument data types
------------------+--------+--------+--------------------- s1.foo | public | getfoo | integer
(1 row)
-- this will work
SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper
-------+----------+---------+------- 1 | 1 | Joe | JOE
(1 row)
HTH,
Joe
