current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
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 Dec 28 '12 at 6:49
add comment

2 Answers

up vote 1 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 –  vg123 Dec 28 '12 at 9:56
    
@mona: Sounds like you may be creating an infinite loop with your triggers. It' your responsibility to avoid that. –  Erwin Brandstetter Dec 28 '12 at 12:28
    
@ Erwin Brandstetter : Yes It was my mistake.Thanks your solution worked. :) :) –  vg123 Dec 29 '12 at 10:12
add comment
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 –  vg123 Dec 28 '12 at 7:10
    
yes.I got the problem.It was causing due to version only.I was using version 9.0.Thanks –  vg123 Dec 28 '12 at 8:16
    
@mona: Once you upgrade: a cast to text is not necessary, text being the default type for quoted literals. –  Erwin Brandstetter Dec 28 '12 at 8:17
add comment

Your Answer

 
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.