Skip site navigation (1) Skip section navigation (2)

Function does not see relations within schema it belongs to?

From: "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Function does not see relations within schema it belongs to?
Date: 2004-07-23 21:56:02
Message-ID: 20040723215602.GD72022@dyatel.antar.bryansk.ru (view raw or flat)
Thread:
Lists: pgsql-bugs
Consider the following example for 7.4.3

% cat test.sql
CREATE SCHEMA test_schema;
SET search_path TO test_schema;
CREATE TABLE test_table (
	x	serial,
	s	bigint
) WITHOUT OIDS;

CREATE FUNCTION test_update_s() RETURNS trigger AS '
BEGIN
	SELECT INTO NEW.s sum(x) FROM test_table;
	RETURN NEW;
END' LANGUAGE 'plPgSQL';

CREATE TRIGGER test_update_s_trg BEFORE INSERT OR UPDATE ON test_table
FOR EACH ROW EXECUTE PROCEDURE test_update_s();

SET search_path TO public; -- here's the key
INSERT INTO test_schema.test_table DEFAULT VALUES;

Then:

fduch(at)~=# \i test.sql
CREATE SCHEMA
SET
psql:test.sql:6: NOTICE:  CREATE TABLE will create implicit sequence "test_table_x_seq" for "serial" column "test_table.x"
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
SET
psql:test.sql:18: ERROR:  relation "test_table" does not exist
CONTEXT:  PL/pgSQL function "test_update_s" line 2 at select into variables

So, the function written WITHIN test_schema and FOR work with
test_schema doesn't see relations inside test_schema until the
search_path points to it?

The same for (at least inlined) SQL functions.

Of course, the workaround is to fully qualify test_schema.test_table
within the function, or to set appropriate search_path when working with
that table/function, but...

But the following works:

fduch(at)~=# SET search_path TO test_schema;
SET
fduch(at)~=# INSERT INTO test_table DEFAULT VALUES ;
INSERT 0 1
fduch(at)~=# SET search_path TO public;
SET
fduch(at)~=# INSERT INTO test_schema.test_table DEFAULT VALUES ;
INSERT 0 1

AFAIK plPgSQL function, have been compiled once, caches query plans so
it directly refers tables (by oids or something else).
Is that the reason for the last insert to work?


-- 
Fduch M. Pravking

Responses

pgsql-bugs by date

Next:From: Alexander M. PravkingDate: 2004-07-23 22:07:18
Subject: Re: Function does not see relations within schema it belongs to?
Previous:From: André GomesDate: 2004-07-23 21:03:06
Subject: VACUUM ANALYZE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group