andehhh andehhh - 3 months ago 10
SQL Question

Postgres usage of format()

Is the usage of format() in cases like this generally interchangeable?

exec_str := format('UPDATE ' || TG_ARGV[0] ||
' SET username = current_user,
time = current_timestamp::timestamp(0);'
);
EXECUTE exec_str;


vs.

exec_str := 'UPDATE ' || TG_ARGV[0] ||
' SET username = current_user,
time = current_timestamp::timestamp(0);'
;
EXECUTE format(exec_str);

Answer

The primary benefit of the function format() is that you can use parameters:

execute format('
    UPDATE %I 
    SET username = current_user,
        time = current_timestamp::timestamp(0);',
    TG_ARGV[0]);

Read more in the documentation.