How to use EXECUTE FORMAT ... USING in Postgres function

Question

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;

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.

Which version of PostgreSQL are you using? AFAIK, the format function was added in 9.1. — mu is too short
– mu is too short, Commented Dec 28, 2012 at 6:49

Erwin Brandstetter · Accepted Answer · 2023-06-12 01:05:56Z

30

Your function could look like this in Postgres 9.0 or later:

CREATE OR REPLACE FUNCTION dummytest_insert_trigger()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
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
$func$;

About RETURN NULL:

To ignore result in BEFORE TRIGGER of PostgreSQL?

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

Are PostgreSQL column names case-sensitive?

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?

Aside: this kind of trigger is obsolete with declarative partitioning in Postgres 10 or later.

edited Jun 12, 2023 at 1:05

answered Dec 28, 2012 at 8:27

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

vg123 Over a year ago

:: 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

Erwin Brandstetter Over a year ago

@mona: Sounds like you may be creating an infinite loop with your triggers. It' your responsibility to avoid that.

vg123 Over a year ago

@ Erwin Brandstetter : Yes It was my mistake.Thanks your solution worked. :) :)

Ankur Srivastava Over a year ago

This was perfect ."There is really no point in using dynamic SQL with EXECUTE as long as you have a static table name. ".

Rafs Over a year ago

Why NEW.id is not pluggable in FORMAT and has to be handled by USING from EXECUTE?

|

neoneye · Accepted Answer · 2020-08-08 08:38:16Z

8

You need explicit cast to text:

EXECUTE format('INSERT INTO %I VALUES ($1,$2)'::text ,v_partition_name) using NEW.id,NEW.datetime;

edited Aug 8, 2020 at 8:38

neoneye

52.5k25 gold badges171 silver badges154 bronze badges

answered Dec 28, 2012 at 7:01

mys

2,49319 silver badges23 bronze badges

2 Comments

vg123 Over a year ago

yes.I got the problem.It was causing due to version only.I was using version 9.0.Thanks

Erwin Brandstetter Over a year ago

@mona: Once you upgrade: a cast to text is not necessary, text being the default type for quoted literals.

Collectives™ on Stack Overflow

How to use EXECUTE FORMAT ... USING in Postgres function

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related