Re: [SQL] function fails where individual statements succeed
Rosser Schwarz <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] function fails where individual statements succeed
while you weren't looking, Tom Lane wrote: [...] > 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. We've opted to simply script issuing the statements serially, rather than as a function for this one. Your explanation makes sense, Tom, though I'm curious why it works in our long-running case (beyond the issue of fewer indices there). Given that, I'm going to move that we adopt the serial issuance approach for that process, to save off the error eventually cropping up there, too. Dankeschoen, /rls -- :wq ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] function fails where individual statements succeed
Rosser Schwarz <[EMAIL PROTECTED]> writes: > We've opted to simply script issuing the statements serially, rather > than as a function for this one. Your explanation makes sense, Tom, > though I'm curious why it works in our long-running case (beyond the > issue of fewer indices there). Does the other case have any partial indexes? The particular error was about an index predicate condition; it might be that you don't happen to fall foul of the issue when dealing with simple CREATE INDEX commands. I'd describe that as "miraculously managing not to fail", though, not as a behavior I'd care to rely on. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function fails where individual statements succeed
while you weren't looking, Tom Lane wrote: > Does the other case have any partial indexes? No it doesn't, and I caught it on reflection. > I'd describe that as "miraculously managing not to > fail", though, not as a behavior I'd care to rely on. Most definitely not; it's already changed. /rls -- :wq ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
