up vote 0 down vote favorite 
CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
v_partition_name    VARCHAR(32);
        BEGIN
        IF NEW.datetime IS NOT NULL THEN
                v_partition_name := 'dummyTest';            
                EXECUTE format('INSERT INTO %I VALUES ($1,$2)',v_partition_name)using NEW.id,NEW.datetime;              
                END IF;                    
           RETURN NULL;
        END;
        $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION dummytest_insert_trigger()
  OWNER TO postgres;

I'm trying to insert using insert into dummyTest values(1,'2013-01-01 00:00:00+05:30');

But it's showing error as 

ERROR: function format(unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function "dummytest_insert_trigger" line 8 at EXECUTE statement

I'm unable get the error.

share|improve this question
1  
Which version of PostgreSQL are you using? AFAIK, the format function was added in 9.1. – mu is too short 14 hours ago
feedback

2 Answers

up vote 0 down vote accepted

Your function could look like this in Postgres 9.0:

CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
   v_partition_name text := quote_ident('dummyTest');  -- assign at declaration
BEGIN
   IF NEW.datetime IS NOT NULL THEN
      EXECUTE 
      'INSERT INTO ' || v_partition_name || ' VALUES ($1,$2)'
      USING NEW.id, NEW.datetime;              
   END IF;                    

   RETURN NULL;  -- You sure about this?
END
$BODY$
  LANGUAGE plpgsql;

I would strongly advice not to use mixed case identifiers. With format( .. %I ..) or quote_ident(), you'd get a table named "dummyTest", which you'll have to double quote for the rest of its existence.
Use lower case instead:

quote_ident('dummytest')

There is really no point in using dynamic SQL with EXECUTE as long as you have a static table name. But that's probably just the simplified example?

share|improve this answer
:: yes.It's a simplified eample.Actually I'm using database partitioning for that table name is generating dynamically ,still IT's not working for simpler part. I used all above with dummytest in lowercase on postgres 9.1 and I'm getting following error ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. CONTEXT: SQL statement "INSERT INTO dummytest VALUES ($1,$2)" PL/pgSQL function "dummytest_insert_trigger" line 7 at EXECUTE statement – mona 11 hours ago
@mona: Sounds like you may be creating an infinite loop with your triggers. It' your responsibility to avoid that. – Erwin Brandstetter 8 hours ago
feedback
up vote 0 down vote

You need eplxicit cast to text:

EXECUTE format('INSERT INTO %I VALUES ($1,$2)'::text ,v_partition_name) using NEW.id,NEW.datetime;
share|improve this answer
ok.I'll check this – mona 13 hours ago
yes.I got the problem.It was causing due to version only.I was using version 9.0.Thanks – mona 12 hours ago
@mona: Once you upgrade: a cast to text is not necessary, text being the default type for quoted literals. – Erwin Brandstetter 12 hours ago
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.