Thread: newbie pl/pgsql question on trigger function error

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".

Wei Wang wrote:

>ERROR:  syntax error at or near ";" at character 32
>CONTEXT:  PL/pgSQL function "trigtest_test1" line 26 at execute statement
>
>         --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 );'';
>
>

Look closely at the CREATE TABLE statement.

It's all higgledeepiggledee!

Try this:

my_query := ''create table '' || quote_ident(NEW.tablename)
         || '' ( arg1 '' || quote_ident(NEW.arg1) || '');'';


--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Mark Gibson <gibsonm@cromwell.co.uk> writes:
> Try this:

> my_query := ''create table '' || quote_ident(NEW.tablename)
>          || '' ( arg1 '' || quote_ident(NEW.arg1) || '');'';

quote_ident() seems appropriate for the table name, but you might want
to leave it off the type name.  For instance, if the argument is
'varchar(20)' then quote_ident would mess things up.

            regards, tom lane