Re: pl/pgsql function not working

On 19 Jun 2002 09:25:11 -0400
Tom Jenkins <tjenkins@devis.com> wrote:


> 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  ;


Try here instead of PERFORM.

      EXECUTE ''update '' || obj.relname || '' set lastaccess = NULL;'';


>   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?



Regards,
Masaru Sugawara