CODEWITHSUNDEEP
postgresqltriggersplpgsqldynamic-sql
Benjamin
Benjamin

Reputation: 25

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?

Upvotes: 1

Views: 3281

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656411

Best with the USING clause of EXECUTE:

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

EXECUTE does not require parentheses.
And you are aware that identifiers are folded to lower case unless quoted where necessary: format specifier %I instead of %s for format() - which also defends against SQL injection attempts.

More details:

Upvotes: 5

Related Questions