pl/pgsql function not working - Mailing list pgsql-general

From Tom Jenkins
Subject pl/pgsql function not working
Date
Msg-id 1024493111.583.62.camel@asimov
Whole thread Raw
Responses Re: pl/pgsql function not working
Re: pl/pgsql function not working
List pgsql-general
hello all,
i hope somebody can shed some light on a problem i'm having with
pl/pgsql.  I need to loop over all my tables and clear out the field
lastaccess (it was improperly defined as time instead of timestamp)

DROP FUNCTION clear_lastaccess();
CREATE FUNCTION clear_lastaccess() RETURNS bool AS '
DECLARE
  obj RECORD;
BEGIN
  FOR obj IN SELECT relname FROM pg_class WHERE relkind IN (''r'') AND
relname !~ ''^pg_'' LOOP
    RAISE NOTICE ''update % set lastaccess = NULL;'', obj.relname;
    PERFORM ''update % set lastaccess = NULL;'', obj.relname  ;
  END LOOP;
  RETURN true;
END;
'LANGUAGE 'plpgsql';

running this function with:
SELECT clear_lastaccess();
results in the update statements getting printed but the updates are
never processed.

looking at my pg log shows the SELECT relname query but no UPDATEs

what do i have wrong?

--

Tom Jenkins
Development InfoStructure
http://www.devis.com



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Pg 7.2B5 -> 7.2.1
Next
From: "philip johnson"
Date:
Subject: database size