Postgres usage of format()

Question

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);

yes, both work. I just would like to know if one of the variants could produce errors. I just did not have any problems so far. Better be safe than sorry ;) — andehhh
– andehhh, Commented Aug 29, 2016 at 10:54

klin · Accepted Answer · 2016-08-29 11:10:23Z

2

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]);

2 Comments

Laurenz Albe Over a year ago

Wouldn't it be better to use EXECUTE ... USING ... for that?

pozs Over a year ago

@LaurenzAlbe USING can only bind values, not identifiers (like in the original question).

Collectives™ on Stack Overflow

Postgres usage of format()

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related