From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rosser Schwarz <rosser.schwarz@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: function fails where individual statements succeed
Date: Mon, 30 Aug 2004 16:32:57 -0400
Message-ID: <11133.1093897977@sss.pgh.pa.us> (raw)
In-Reply-To: <37d451f7040830101763682930@mail.gmail.com>
References: <37d451f7040830101763682930@mail.gmail.com>
Rosser Schwarz <rosser.schwarz@gmail.com> writes:
> When we group the statements together and wrap them in
> a CREATE FUNCTION, running the function fails with:
> ERROR: index expressions and predicates may refer only to the table
> being indexed
I believe what is happening is that the entire SQL function is parsed,
analyzed, and planned before we start to execute it. By the time
control arrives at the CREATE INDEX command, the table named
certegy.masterpcb is not the same one that that name referred to when
the function's statements were parsed. I haven't bothered to work out
exactly how that leads to this particular error message rather than some
other failure, but I can say that this isn't gonna work out well in general.
If you want to encapsulate this set of operations in a function, I'd
suggest using plpgsql and being careful to EXECUTE each query rather
than letting plpgsql try to cache a plan for it.
regards, tom lane
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: tgl@sss.pgh.pa.us, rosser.schwarz@gmail.com
Subject: Re: function fails where individual statements succeed
In-Reply-To: <11133.1093897977@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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