To: Rosser Schwarz <rosser.schwarz@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: function fails where individual statements succeed 
In-reply-to: <37d451f7040830101763682930@mail.gmail.com> 
References: <37d451f7040830101763682930@mail.gmail.com>
Comments: In-reply-to Rosser Schwarz <rosser.schwarz@gmail.com>
	message dated "Mon, 30 Aug 2004 12:17:29 -0500"
Date: Mon, 30 Aug 2004 16:32:57 -0400
Message-ID: <11133.1093897977@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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