How to pass NEW.* to EXECUTE in trigger function

问题:

I have a simple mission is inserting huge MD5 values into tables (partitioned table), and have created a trigger and also a trigger function to instead of INSERT operation. And in function I checked the first two characters of NEW.md5 to determine which table should be inserted.

DECLARE
  tb text;
BEGIN
  IF TG_OP = 'INSERT' THEN
    tb = 'samples_' || left(NEW.md5, 2);
    EXECUTE(format('INSERT INTO %s VALUES (%s);', tb, NEW.*)); <- WRONG
  END IF;
  RETURN NULL;
END;

The question is how to concat the NEW.* into the SQL statement?

回答1:

Best with the USING clause of EXECUTE:

CREATE FUNCTION foo ()
  RETURNS trigger AS
$func$
BEGIN
  IF TG_OP = 'INSERT' THEN
     EXECUTE format('INSERT INTO %s SELECT $1.*'
                  , 'samples_' || left(NEW.md5, 2);
     USING NEW;
  END IF;
  RETURN NULL;
END
$func$ LANGUAGE plpgsql;

And EXECUTE does not require parentheses.
And you are aware that identifiers are folded to lower case unless quoted where necessary (%I instead of %s in format()).

More details:

• INSERT with dynamic table name in trigger function
• How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?