-----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/6q65ood55Uv4ElYRAhl7AJ0W0wkbXhpC9YbPj0dq1PhPyihU3g Cfd5am
rMm/kvuoEDVrQagO1pa27FQ=
=yfyW
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html