Re: function fails where individual statements succeed

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rosser Schwarz <rosser(dot)schwarz(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: function fails where individual statements succeed
Date: 2004-08-30 20:32:57
Message-ID: 11133.1093897977@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Rosser Schwarz <rosser(dot)schwarz(at)gmail(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rosser Schwarz 2004-08-30 20:40:23 Re: function fails where individual statements succeed
Previous Message Rosser Schwarz 2004-08-30 18:34:23 Re: function fails where individual statements succeed