create one function to let other user execute vacuum command. got such an error.

On Tue, 2012-03-27 at 18:51 -0700, leaf_yxj wrote:

create one function to let other user execute vacuum command. got such an
error. Please help. Thanks. Regards . Grace

rrp=> create function vacuum_f ( tablename char(100))
Returns char(100) AS $$
vacuum tablename;
$$ Language plpgsql security definer;
ERROR: syntax error at or near "vacuum"
LINE 3: vacuum tablename;
^
rrp=>

Well, first, it doesn't follow PL/pgsql guidelines. You need at least a
BEGIN at the beginning of the function, and an END at the end.

But even with this, you cannot use VACUUM in a function. Don't remember
the reason why right now, but all you'll get is this error:

ERROR: VACUUM cannot be executed from a function or multi-command
string

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

On 28 Mar 2012, at 07:44, Guillaume Lelarge wrote:

On Tue, 2012-03-27 at 18:51 -0700, leaf_yxj wrote:

create one function to let other user execute vacuum command. got such an
error. Please help. Thanks. Regards . Grace

rrp=> create function vacuum_f ( tablename char(100))
Returns char(100) AS $$
vacuum tablename;
$$ Language plpgsql security definer;
ERROR: syntax error at or near "vacuum"
LINE 3: vacuum tablename;
^
rrp=>

Well, first, it doesn't follow PL/pgsql guidelines. You need at least a
BEGIN at the beginning of the function, and an END at the end.

But even with this, you cannot use VACUUM in a function. Don't remember
the reason why right now, but all you'll get is this error:

ERROR: VACUUM cannot be executed from a function or multi-command
string

Because its not a transaction safe operation.
Beside's , what's with the char(100) ? Function like that,you should use text type.