X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 04970D1B1DC for ; Tue, 29 Jun 2004 19:28:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59343-06 for ; Tue, 29 Jun 2004 22:28:00 +0000 (GMT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 7A48ED1B241 for ; Tue, 29 Jun 2004 19:27:57 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 1882657; Tue, 29 Jun 2004 15:19:59 -0700 Message-ID: <40E1ECEF.5090007@joeconway.com> Date: Tue, 29 Jun 2004 15:27:59 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040510 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jean-Denis Giguere Cc: pgsql-admin@postgresql.org Subject: Re: grant execute on many functions References: <40E1E1A9.8080207@fastmail.fm> In-Reply-To: <40E1E1A9.8080207@fastmail.fm> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/381 X-Sequence-Number: 14041 Jean-Denis Giguere wrote: > But, the problem is more complex for functions because you have to give > the arguments. You can find the list in the pg_proc table but they are > store in a array by oid. So you can resolve the type with the table > pg_type. After, you have to reproduce the correct syntax (with correct > number of orgument, on one execute line...) > Does this help? SELECT n.nspname || '.' || p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')' FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proname ~ '^format_type$'; ?column? -------------------------------------- pg_catalog.format_type(oid, integer) (1 row) Joe