pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEXECUTE problem on schema
4+ messages / 2 participants
[nested] [flat]
* EXECUTE problem on schema
@ 2003-03-27 01:54 jack <datactrl@tpg.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: jack @ 2003-03-27 01:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: EXECUTE problem on schema
@ 2003-03-27 10:11 Ian Barwick <barwick@gmx.net>
parent: jack <datactrl@tpg.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Ian Barwick @ 2003-03-27 10:11 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: EXECUTE problem on schema
@ 2003-03-28 00:59 jack <datactrl@tpg.com.au>
parent: Ian Barwick <barwick@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: jack @ 2003-03-28 00:59 UTC (permalink / raw)
To: pgsql-sql; Ian Barwick <barwick@gmx.net>
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: EXECUTE problem on schema
@ 2003-03-28 08:34 Ian Barwick <barwick@gmx.net>
parent: jack <datactrl@tpg.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Ian Barwick @ 2003-03-28 08:34 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-28 08:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-27 01:54 EXECUTE problem on schema jack <datactrl@tpg.com.au>
2003-03-27 10:11 ` Ian Barwick <barwick@gmx.net>
2003-03-28 00:59 ` jack <datactrl@tpg.com.au>
2003-03-28 08:34 ` Ian Barwick <barwick@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox