function fails where individual statements succeed - Mailing list pgsql-sql

We have a SQL function that encapsulates a daily bulk import/update
process.  The statements making up the function succeed when run
individually.  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
CONTEXT:  SQL function "masterfilepcb_load" statement 14

As well, when run in the function, the table masteryesterday is not
populated, while when the statements are run individually, it is.

The function is kind of a hack, but it should work; we have a similar
function running a similar process that we've used daily without
problem for months.  It works like so: daily, we receive a file that
contains exhaustive data on our customers' accounts, which we use to
update our records of the accounts.  These functions move the current
version of the imported data to a "yesterday" table and then bring in
the current day's data.  I did some digging in indexcmds.c, after
grepping for the error message, but didn't see anything that might
explain the problem. The error suggests pg thinks we're looking at
another table.

The only difference between the functions, other than referring to
different schemas/tables (logically the same, details different) is
that this one returns void, while the one in production returns int,
and has "select 1" as its last statement.  Given that the failure
occurs before that point, I'm somewhat doubtful that's the issue.

The function is:

CREATE OR REPLACE FUNCTION certegy.masterfilepcb_load() RETURNS void AS
'
drop index certegy.ix_certegy_masterpcb_acctno;
drop index certegy.ix_certegy_masterpcb_acctno_prevacct_null;
drop index certegy.ix_certegy_masterpcb_prevacct_notnull;
drop index certegy.ix_certegy_masteryesterdaypcb_acctno;
drop index certegy.ix_certegy_masteryesterdaypcb_acctno_prevacct_null;
drop index certegy.ix_certegy_masteryesterdaypcb_prevacct_notnull;
alter table certegy.masteryesterdaypcb rename to masterpcb_x;
alter table certegy.masterpcb rename to masteryesterdaypcb;
alter table certegy.masterpcb_x rename to masterpcb;
truncate certegy.masterpcb;
copy certegy.masterpcb from \'/tmp/masterpcb.txt\' with delimiter \'|\' null  \'\';
create unique index ix_certegy_masterpcb_acctno on certegy.masterpcb using  btree (acctno);
create unique index ix_certegy_masteryesterdaypcb_acctno on  certegy.masteryesterdaypcb using btree (acctno);
create unique index ix_certegy_masterpcb_acctno_prevacct_null on  certegy.masterpcb using btree (acctno, prevacct)
whereprevacct is null;
 
create unique index ix_certegy_masteryesterdaypcb_acctno_prevacct_null on  certegy.masteryesterdaypcb using btree
(acctno,prevacct) where prevacct is  null;
 
create unique index ix_certegy_masterpcb_prevacct_notnull on  certegy.masterpcb using btree (prevacct) where prevacct
isnot null;
 
create unique index ix_certegy_masteryesterdaypcb_prevacct_notnull on  certegy.masteryesterdaypcb using btree
(prevacct)where prevacct is not  null;
 
' LANGUAGE 'sql' STABLE;

We're using 7.4.2, built from source, on RH9.

Any help is appreciated.

/rls

-- 
:wq