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

[Hou, Zhijie]

Hi Ishii san,

> Consider this more, I think current way to determine whether a function appearing in a query is immutable is not very accurate. Suppose > we have function f1 in "public" and "myschema"
> schema.  When Pgpool-II looks into the query:

> SELECT * FROM table where i = f1();

> it may find public.f1() or myshcema.f1() depending the schema search path. This is because current code works like this:

> 1. In non_immutable_function_call_walker() get function name from parse tree (in this process catalog or schema name part is omitted).

> 2. Then execute following in is_immutable_function():
> SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = '%s' AND p.provolatile = 'i'"
> to know whether the function is immutable or not.

> I think we should pass function name with schema (and catalog name) if any to is_immutable_function() and is_immutable_function() should > call
> to_regproc() to get function oid, then query against pg_proc using the oid. Since to_regproc() considers the schema search path, it 
> should return proper oid we are looking for. Note that, however, older versions of PostgreSQL do not have to_regproc. In this case we have > to fall back to the way we are currently doing.

> What do you think?

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.

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'"

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?

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

Best regards,
houzj