newbie pl/pgsql question on trigger function error
От | Wei Wang |
---|---|
Тема | newbie pl/pgsql question on trigger function error |
Дата | |
Msg-id | 000901c3ef2d$1b8c6b60$726ee880@weiwang обсуждение исходный текст |
Ответы |
Re: newbie pl/pgsql question on trigger function error
|
Список | pgsql-general |
Hi, I'm a complete newbie to pl/pgsql and wrote the following trigger function that's supposed to create a table when a certain row is inserted into another table: When I run insert into trigtest values ('wei', 'int4', 'int5'); I get the error of: ERROR: syntax error at or near ";" at character 32 CONTEXT: PL/pgSQL function "trigtest_test1" line 26 at execute statement I know this is a very naive question, any hint or direction would be highly appreciated. table definition: CREATE TABLE public.trigtest ( tablename text NOT NULL, arg1 text NOT NULL, arg2 text NOT NULL ) WITHOUT OIDS; trigger definition: CREATE TRIGGER trigtest_test1 BEFORE INSERT OR UPDATE ON public.trigtest FOR EACH ROW EXECUTE PROCEDURE public.trigtest_test1(); trigger function: CREATE OR REPLACE FUNCTION trigtest_test1() RETURNS trigger AS ' DECLARE my_query varchar(4000); BEGIN -- check if the arguments are NULL IF NEW.tablename IS NULL THEN RAISE EXCEPTION ''tablename cannot be null''; END IF; IF NEW.arg1 IS NULL THEN RAISE EXCEPTION ''arg1 cannot be null'', NEW.tablename; END IF; IF NEW.arg2 IS NULL THEN RAISE EXCEPTION ''arg2 cannot be null'', NEW.tablename; END IF; --create a table with the name as NEW.tablename, and the first column --called arg1 and the type to be the value of NEW.arg1 my_query := ''create table '' ||quote_ident(NEW.tablename) ||'' ( '' ||quote_ident(NEW.arg1) ||'', arg1 );''; -- Only when NEW.arg1 is int4, we execute the create IF (NEW.arg1 = ''int4'') THEN EXECUTE my_query; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; I don't know what I did wrong here. Also how can I find out what query is actually passed on to postgresql? I turned on the logging in pgadmin III at "Debug".
В списке pgsql-general по дате отправления: