agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Joe Conway <mail@joeconway.com>
To: david williams <dw_remote@hotmail.com>
Cc: bcschnei@attbi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Stored Procedures
Date: Wed, 02 Oct 2002 09:34:57 -0700
Message-ID: <3D9B2031.3020809@joeconway.com> (raw)
References: <DAV54UtmBLKl0ny8hSh00008ef1@hotmail.com>
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
view thread (23+ messages) latest in thread
Message-ID: <3D9B2031.3020809@joeconway.com>
Permalink: ../3D9B2031.3020809@joeconway.com/
Also on: postgresql.org/message-id/3D9B2031.3020809@joeconway.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, dw_remote@hotmail.com, bcschnei@attbi.com
Subject: Re: Stored Procedures
In-Reply-To: <3D9B2031.3020809@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox