Обсуждение: 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