function that creates triggers

[gacaccia]

i want to create a function that will add a trigger to every user table in a database. i tried the following, but it gives me an error nearw keyword "trigger". is what i'm trying to do possible or is the "execute" statement not able to run "create" statements?

CREATE OR REPLACE FUNCTION createtriggers()
  RETURNS void AS
$BODY$
DECLARE
	tables CURSOR for select table_name from alltables;
	table_name alltables.table_name%type;
	strsql varchar(1000);

BEGIN
	open tables;
	loop
		fetch tables into table_name;
		if not found then
			exit;
		end if;
		strsql = 'CREATE OR REPLACE TRIGGER  ' || table_name || '_stamp AFTER INSERT OR UPDATE OR DELETE ON  ' || table_name || ' FOR EACH ROW EXECUTE PROCEDURE process_table_stamp()';
		execute strsql;
	end loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

 

[gacaccia]

never mind. figured it out. didn't realize that you can't use the REPLACE keyword with triggers. removed that and it works great.

 

[ahmet_sinav]

i dont know u are aware but
u have forget
fetch next
command. the loop may be endless.

 

[gacaccia]

the code works as expected, so it doesn't appear that "fetch next" is needed, which does beg the question what does "fetch next" do that is different from just plain old "fetch"? i did a quick search and found examples of other people using the same syntax (just using plain old "fetch").