Thread: pl/pgsql function not working
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
On Wednesday 19 Jun 2002 2:25 pm, Tom Jenkins wrote: > hello all, > i hope somebody can shed some light on a problem i'm having with > pl/pgsql. > PERFORM ''update % set lastaccess = NULL;'', obj.relname ; Are you sure this shouldn't be EXECUTE (build the sql-string normally first) - Richard Huxton
On Wed, 2002-06-19 at 10:09, Richard Huxton wrote: > On Wednesday 19 Jun 2002 2:25 pm, Tom Jenkins wrote: > > hello all, > > i hope somebody can shed some light on a problem i'm having with > > pl/pgsql. > > > PERFORM ''update % set lastaccess = NULL;'', obj.relname ; > > Are you sure this shouldn't be EXECUTE (build the sql-string normally first) > hrmmm.. replacing PERFORM with EXECUTE results in an error NOTICE: Error occurred while executing PL/pgSQL function clear_lastaccess NOTICE: line 6 at execute statement ERROR: query "SELECT 'update % set lastaccess = NULL;', $1 " returned 2 columns -- Tom Jenkins Development InfoStructure http://www.devis.com
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
Perform doesn't work that way from my experience Use the following EXECUTE ''UPDATE ''||obj.relname||'' SET lastaccess = NULL''; The % you are using is generally used in the RAISE NOTICE OR EXCEPTION pieces HTH Darren Ferguson On Wed, 19 Jun 2002, Richard Huxton wrote: > On Wednesday 19 Jun 2002 2:25 pm, Tom Jenkins wrote: > > hello all, > > i hope somebody can shed some light on a problem i'm having with > > pl/pgsql. > > > PERFORM ''update % set lastaccess = NULL;'', obj.relname ; > > Are you sure this shouldn't be EXECUTE (build the sql-string normally first) > > - Richard Huxton > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Darren Ferguson
You can't use execute like that it discards the result unless you use it in a FOR rec IN EXECUTE statement then you can get the results out. EXECUTE ''UPDATE ''||obj.relname||'' set lastaccess = NULL;''; This will work Darren On 19 Jun 2002, Tom Jenkins wrote: > On Wed, 2002-06-19 at 10:09, Richard Huxton wrote: > > On Wednesday 19 Jun 2002 2:25 pm, Tom Jenkins wrote: > > > hello all, > > > i hope somebody can shed some light on a problem i'm having with > > > pl/pgsql. > > > > > PERFORM ''update % set lastaccess = NULL;'', obj.relname ; > > > > Are you sure this shouldn't be EXECUTE (build the sql-string normally first) > > > > hrmmm.. replacing PERFORM with EXECUTE results in an error > > NOTICE: Error occurred while executing PL/pgSQL function > clear_lastaccess > NOTICE: line 6 at execute statement > ERROR: query "SELECT 'update % set lastaccess = NULL;', $1 " returned > 2 columns > > > > > -- Darren Ferguson
Masaru Sugawara, Darren Ferguson, On Wed, 2002-06-19 at 11:35, Masaru Sugawara wrote: > Try here instead of PERFORM. > > EXECUTE ''update '' || obj.relname || '' set lastaccess = NULL;''; this works. thank you folks. -- Tom Jenkins Development InfoStructure http://www.devis.com