1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Benjamin
  • 25
  • 1
  • 5

1 Answers1

5

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • In a trigger function, I was not able to use `NEW` when passed in `FORMAT(.., NEW)`. When I passed it with `EXECUTE 'Query' USING NEW;` I was able to use it `$1.colname`. I thought `FORMAT` parameters are identical to `USING` parameters, but it is not the case.. – Rafs Mar 27 '23 at 11:11
  • @RTD: `EXECUTE` and `format()` are completely different beasts. The former executes dynamic SQL in a separate execution context and you can pass *values* in a `USING` clause (as arbitrary data types) - not SQL keywords or identifiers. Just like with prepared statements. The latter is a plain function concatenating strings (type `text`). You can only pass `text` - or data types with a registered implicit cast to `text`. – Erwin Brandstetter Mar 27 '23 at 16:08