newbie pl/pgsql question on trigger function error

From: "Wei Wang" <ww220(at)cam(dot)ac(dot)uk>
To: "pgsql" <pgsql-general(at)postgresql(dot)org>
Subject: newbie pl/pgsql question on trigger function error
Date: 2004-02-09 16:52:30
Message-ID: 000901c3ef2d$1b8c6b60$726ee880@weiwang
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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".

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Grove 2004-02-09 16:52:42 [ANN] FireStorm/DAO now fully supports Postgres
Previous Message Stephen Howard 2004-02-09 16:49:51 plperlu and 'use' statement scope question