Re: grant permissions to set variable?

On Mar 14, 2007, at 11:36 AM, Richard Huxton wrote:

> Vivek Khera wrote:
>> I want to do some debugging on an app, and I'd like to set on a
>> per-connection basis "set log_min_duration_statement = 10;"
>> Obviously since I'm not super user I get permission denied.
>> Is there some GRANT I can grant to the user in question to allow
>> this?  I don't really want to do it globally or for all
>> connections by that user, both of which are easy to do
>
> Could you handle it with a security=definer function?
>

Good call.  However, the following complains about the $ in $1.  My
guess is that the SET command doesn't like anything but an integer to
be there.  If I make it a string, the function gets defined, but at
runtime it complains that it is not an integer.  If I try to cast the
string to '$1'::integer the function definition again fails with
syntax error.

CREATE OR REPLACE FUNCTION setlogtime(integer) RETURNS void AS $$
   SET log_min_duration_statement = $1;
   SHOW log_min_duration_statement;
$$ LANGUAGE SQL SECURITY DEFINER;


I tried variants '$1' and '$1'::integer as noted above.

How can I write this function?