Is it possible to execute PL/pgSQL not function wrapped? - Mailing list pgsql-novice

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

for development of a installation script I want a PL/pgSQL script that cleans
up a database (drop of all objects there in). I tried to execute PL/pgSQL
code directly within the script:
   DECLARE
      views             RECORD;
      tables            RECORD;
      str_statement     TEXT;
   BEGIN
      -- drop all views
      FOR views IN
         SELECT
               viewname,
               schemaname
            FROM
               pg_views
            WHERE
               viewowner = 'lyrix_dba'
      LOOP
         str_statement := ''drop view '' || views.schemaname || ''.'';
         str_statement := str_statement || views.viewname;
         EXECUTE str_statement;
         commit;
      END LOOP;


      -- drop all tables
      FOR tables IN
         SELECT
               tablename,
               schemaname
            FROM
               pg_tables
            WHERE
               tableowner = 'lyrix_dba'
      LOOP
         str_statement := ''drop table '' || tables.schemaname || ''.'';
         str_statement := str_statement || tables.tablename;
         EXECUTE str_statement;
         commit;
      END LOOP;

      RETURN 0;
   END;

However, this results in a number of parse errors. I suppose I needed to tell
PostgrSQL what language the stuff is written in, but how? So is it possible
to do it the way intend or do I need to wrap the code in a function that I
create in the script, and drop the function after usage anyway?

Cheers

Thiemo

- --
root ist die Wurzel allen Übels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/6q65ood55Uv4ElYRAhl7AJ0W0wkbXhpC9YbPj0dq1PhPyihU3gCfd5am
rMm/kvuoEDVrQagO1pa27FQ=
=yfyW
-----END PGP SIGNATURE-----