Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id BE5B56369AD
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu,  5 Mar 2009 14:16:00 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 11375-02-2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu,  5 Mar 2009 14:15:56 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail.gmx.net (mail.gmx.net [213.165.64.20])
	by mail.postgresql.org (Postfix) with SMTP id 232316359D9
	for <pgsql-admin@postgresql.org>; Thu,  5 Mar 2009 12:41:50 -0400 (AST)
Received: (qmail 12063 invoked by uid 0); 5 Mar 2009 16:41:49 -0000
Received: from 192.124.237.8 by www100.gmx.net with HTTP;
	Thu, 05 Mar 2009 17:41:49 +0100 (CET)
Content-Type: text/plain; charset="iso-8859-1"
Date: Thu, 05 Mar 2009 17:41:49 +0100
From: Jan-Peter.Seifert@gmx.de
In-Reply-To: <20090305003259.GA24998@raf.org>
Message-ID: <20090305164149.228110@gmx.net>
MIME-Version: 1.0
References: <20090302122020.137490@gmx.net> <49ABE46D.3000501@synechron.com>
	<5521.1236006477@sss.pgh.pa.us> <20090304144122.153400@gmx.net>
	<20090305003259.GA24998@raf.org>
Subject: Re: getting 'full' names of functions?
To: raf <raf@raf.org>, pgsql-admin@postgresql.org
X-Authenticated: #955937
X-Flags: 0001
X-Mailer: WWW-Mail 6100 (Global Message Exchange)
X-Priority: 3
X-Provags-ID: V01U2FsdGVkX18kBcU70EQk5VCP9+gEDNc9q1bQqpzfz+IoE5fU6S
	5EJs65JGy7jpTtv5E7bexmvidWydVp5z6zMw==
Content-Transfer-Encoding: 8bit
X-GMX-UID: onXZelYXeWUkUFPwZ29nyAgjL0tsZs2N
X-FuHaFi: 0.6
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200903/76
X-Sequence-Number: 31576

Hello raf,

> > > Easier is just
> > > 	select oid::regprocedure from pg_proc where <whatever>

> note that this method doesn't produce a complete function
> signature. the precision and scale of numerics are not
> included in the output. hopefully, that won't matter for
> your needs.

Oh. So functions expecting e.g. numeric(5,2) as argument wouldn't be listed correctly? Is this going to be fixed then? For now all I need is the 'full' function name necessary for GRANT/REVOKE.

I guess this will do for now for 'non-numeric'-functions?:

SELECT DISTINCT p.oid::regprocedure::text FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE nspname !~* '^pg_' AND nspname != 'information_schema'

Thank you very much,

Peter
-- 
Computer Bild Tarifsieger! GMX FreeDSL - Telefonanschluss + DSL
für nur 17,95 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a