{"id":36731,"date":"2024-08-15T10:52:14","date_gmt":"2024-08-15T00:52:14","guid":{"rendered":"https:\/\/database.guide\/?p=36731"},"modified":"2024-08-15T10:52:16","modified_gmt":"2024-08-15T00:52:16","slug":"using-the-variadic-keyword-with-the-format-function-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/using-the-variadic-keyword-with-the-format-function-in-postgresql\/","title":{"rendered":"Using the VARIADIC Keyword with the FORMAT() Function in PostgreSQL"},"content":{"rendered":"\n

When we use the format()<\/code> function in PostgreSQL, we can pass any number of strings to insert into the resulting formatted string in positions that are specified in the first argument. When we do this, we might typically pass the strings as separate arguments. But this isn’t the only way to do it.<\/p>\n\n\n\n

We have the option of passing the strings as array elements. That is, we can pass an array to the function, and have it extract each array element as though it’s a separate argument to insert into the formatted string.<\/p>\n\n\n\n\n\n\n\n

The reason we can do this is because the format()<\/code> function is variadic. This means it accepts a variable number of arguments. If we use the VARIADIC<\/code> keyword when passing an array, this will result in each array element being treated as a separate argument to insert into the formatted string.<\/p>\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s an example to demonstrate:<\/p>\n\n\n\n

SELECT format(\n    'I have a %s, a %s, and a %s', \n    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
I have a Cat, a Dog, and a Horse<\/pre>\n\n\n\n
Here I used the VARIADIC<\/code> keyword along with the ARRAY<\/code> constructor<\/a> as the second argument to the format()<\/code> function. This resulted in each array element being treated as a separate string to be inserted into the output string.<\/p>\n\n\n\n
This is basically the same effect as passing each array element as a separate argument:<\/p>\n\n\n\n
SELECT format(\n    'I have a %s, a %s, and a %s', \n    'Cat', 'Dog', 'Horse'\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
I have a Cat, a Dog, and a Horse<\/pre>\n\n\n\n
Changing the Position of Elements<\/h2>\n\n\n\n
We can switch the position of each element if we want:<\/p>\n\n\n\n
SELECT format(\n    'I have a %2$s, a %3$s, and a %1$s', \n    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
I have a Dog, a Horse, and a Cat<\/pre>\n\n\n\n
SQL Literals<\/h2>\n\n\n\n
We can also do other stuff, like use the array elements as SQL literals:<\/p>\n\n\n\n
SELECT format(\n    'SELECT * FROM t1 WHERE c2 = %2$L', \n    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
SELECT * FROM t1 WHERE c2 = 'Dog'<\/pre>\n\n\n\n
SQL Identifiers<\/h2>\n\n\n\n
We can even use the array elements as SQL identifiers:<\/p>\n\n\n\n
SELECT format(\n    'SELECT * FROM %2$I', \n    VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ]\n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
SELECT * FROM \"Dog\"<\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
When we use the format() function in PostgreSQL, we can pass any number of strings to insert into the resulting formatted string in positions that are specified in the first argument. When we do this, we might typically pass the strings as separate arguments. But this isn’t the only way to do it. We have … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[169,10,84],"class_list":["post-36731","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-arrays","tag-how-to","tag-string"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36731"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36731\/revisions"}],"predecessor-version":[{"id":36738,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36731\/revisions\/36738"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}