[pgpool-hackers: 3725] Re: problem when getting function name

[Tatsuo Ishii]

Hi Zhijie,

> Yes, I agree, current code can not distinguish different schema.
> 
> 1.However, the code of to_regproc() is as follows:
> -----------------------------------------------------------
> Datum
> to_regproc(PG_FUNCTION_ARGS)
> {
> 	...
> 	names = stringToQualifiedNameList(pro_name);
> 	clist = FuncnameGetCandidates(names, -1, NIL, false, false, true);
> 
> 	if (clist == NULL || clist->next != NULL)
> 		PG_RETURN_NULL();
> 
> 	PG_RETURN_OID(clist->oid);
> }
> -----------------------------------------------------------
>
> If we have overloaded functions in the same schema, to_regproc() return NULL.
>
> In this case, we can not use to_regproc() to get function oids.

Oh...

> Personlly, I usually use the following sql to to determine whether a function appearing in a query is immutable
> 
> 	" SELECT count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace as n 
>            WHERE p.proname = '%s<function_name>' 
>             AND n.oid = p.pronamespace 
>             AND n.nspname = '%s<schema_name>' 
>             AND p.provolatile = 'i'"

Looks good. If we want to be more rigorous, we should consider the
function args but in practice no one wants to mix immutable and
non-immutable functions having different arguments (overloading). So I
think this is enough.

> Or, we can create a function like pool_to_regprocs() which return oids.
> 	"SELECT count(*) FROM pg_catalog.pg_proc p, pool_to_regprocs('schemaname.funcname') n 
> 	  where p.provolatile = 'i' AND n.foid = p.oid;"

> What do you think?

Creating C function could be increase maintenace work. So I would like
not to prefere this if we can avoid it.

> 2. For catalog name, 
> since cross-database references have not been implemented in postgresql, may be we can ignore catalog name?

Agreed.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp