In postgres 9.1 I'd like to create a function that takes an index name, a table name and a variable number of columns, constructs an index, and then does some other things.
My current approach is to use plpgsql and construct a dynamic command to execute. However, I'm getting tripped up when trying to use quote_ident
to protect all of the identifiers. Code thus far:
CREATE OR REPLACE FUNCTION my_create_index(indexname text, tablename text, VARIADIC arr text[]) RETURNS void AS $$
DECLARE
command_string text;
BEGIN
command_string := 'CREATE INDEX ' || quote_ident(indexname) || ' ON ' ||
quote_ident(tablename) || ' (' ||
format(repeat('%I ', array_length($3, 1)), VARIADIC $3) ||
')';
-- display the string
RAISE NOTICE '%', command_string;
-- execute the string
EXECUTE command_string;
-- (do other stuff)
END;
$$ LANGUAGE plpgsql;
The code appears to be successful when passing one column name, but with two or more I get the following error:
ERROR: too few arguments for format
What am I doing wrong? (Presumably something with format
or my use of VARIADIC
.)
Thank you!
You don't generally to use both format
and quote_ident
. format
is smart enough to do identifer quoting its self; you show that yourself in part of your expression, while using unnecessary concatenation and quote_ident
calls elsewhere.
However, I'm seeing the same issue you are with calling format
with a VARIADIC
array argument, and I suspect you've found a bug.
Here's a workaround until I spot what's going on:
command_string := format('CREATE INDEX %I ON %I (%s)',
indexname, tablename, (
SELECT string_agg(quote_ident(x), ', ')
FROM unnest($3) x
)
);
Note that in your original code, '%I '
should've been '%I, '
.
Yes, bug confirmed, and it's been reported before but looks like it never got checked up on. The same bug exists in concat
and concat_ws
. These functions fail to check for the VARIADIC
argument flag.
Observe:
regress=> SELECT format('%I', VARIADIC ARRAY['b','c','d']);
format
-----------
"{b,c,d}"
(1 row)
regress=> SELECT format('%I', 'b','c','d');
format
--------
b
(1 row)
regress=> SELECT format('%I, %I, %I', VARIADIC ARRAY['b','c','d']);
ERROR: too few arguments for format
The only workaround I can think of is either avoiding them (as shown above) or using EXECUTE
to construct the function call dynamically. In the case of format
that's totally redundant, so I'd just use the above sub-query with unnest
.
I'll try to chase this up on the PostgreSQL mailing lists, but I'm going to be insanely busy next week, so poke me or post on pgsql-bugs with a link back to this question yourself if you don't see a follow-up by mid week-after-next.