Postgres Pro
Downloads
Home   >   mailing lists

Thread: EXECUTE problem on schema

EXECUTE problem on schema

From
"jack"
Date:
Hi, all

postgreSQL v7.3.3

I found EXECUTE on pl/pgsql doesn't support schema. When I specify a table
such as "public.tablename", it reported "not such a relation object". The
only SQL statement I used is "update". Don't know wether other statements
have the same problem.

Jack

Re: EXECUTE problem on schema

From
Ian Barwick
Date:
On Thursday 27 March 2003 02:54, jack wrote:
> Hi, all
>
> postgreSQL v7.3.3

Are you sure? The latest release is 7.3.2.

> I found EXECUTE on pl/pgsql doesn't support schema. When I specify a table
> such as "public.tablename", it reported "not such a relation object". The
> only SQL statement I used is "update". Don't know wether other statements
> have the same problem.

Can you post an example?

Ian Barwick
barwick@gmx.net

Re: EXECUTE problem on schema

From
"jack"
Date:
Ian,

Sorry about the version mistake. It's V7.3.2. And the following are the
example.

Jack

CREATE TABLE test_j2(c1 varchar (2),n2 integer,
PRIMARY KEY(c1, n2)
);
/*
a1 100
b1 200
*/

/*
Calling tesp_test1 with 'test_j2' is ok, while calling with 'public.test_j2'
will fail.

*/

CREATE OR REPLACE FUNCTION tesP_test1( NAME) RETURNS INTEGER AS'
BEGIN
EXECUTE ''UPDATE ''||quote_ident($1)||'' SET n2 = 1'';RETURN 0;

END;'LANGUAGE 'PLPGSQL';

Re: EXECUTE problem on schema

From
Ian Barwick
Date:
On Friday 28 March 2003 01:59, jack wrote:

(...)
>  And the following are the example.

Aha, the problem is not EXECUTE but QUOTE_IDENT:
 test=# SELECT quote_ident('public.test_j2');    quote_ident     ------------------  "public.test_j2" (1 row)
 test=# SELECT * FROM "public.test_j2"; ERROR:  Relation "public.test_j2" does not exist

which is correct, because schema and relation names can contain periods.
To quote a schema-qualified relation name you need something like
"public"."test_j2" (or "public"."table.with_period_in_name") which
quote_indent can't handle because it doesn't know whether the
period is a schema / relation name divider or part of the relation name.

This has come up before, see:
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00892.php

It would probably be easiest to leave out the quote_ident from your
function definition and do the quoting manually in the function call,
if necessary, which mostly isn't.

Ian Barwick
barwick@gmx.net