I was trying to see if it was possible to create an 'internal' function 
after bootstrap (i.e. without listing in pg_proc.h). The test case below 
illustrates that it is indeed possible.

test=# CREATE OR REPLACE FUNCTION mytest(text,int,int) RETURNS text AS 
'text_substr' LANGUAGE 'internal' IMMUTABLE STRICT;
CREATE FUNCTION
test=# select mytest('abcde',2,2);
  mytest
--------
  bc
(1 row)

It made me wonder why don't we always create internal functions this 
way, or at least all except a core set of bootstrapped functions. Am I 
wrong in thinking that it would eliminate the need to initdb every time 
a new internal function is added?

We could have a script, say "internal_functions.sql", that would contain 
"CREATE OR REPLACE FUNCTION...LANGUAGE 'internal'..." for each internal 
function and be executed by initdb. When a new builtin function is added 
to the backend, you could run this script directly to update your catalog.

Just a thought. Any reason we can't or don't want to do this?

Joe


Sat, 13 Jul 2002 17:50:38 -0700
Joe Conway <mail@joeconway.com>
question re internal functions requiring initdb
Joe Conway <mail@joeconway.com> writes:
> It made me wonder why don't we always create internal functions this 
> way, or at least all except a core set of bootstrapped functions.

I don't believe it will actually work: you *must* add an internal
function to include/catalog/pg_proc.h, or it won't get into the function
lookup table that's built by Gen_fmgrtab.sh.

It is true that you don't have to force an initdb right away, but
there's an efficiency penalty IIRC (can't bypass the lookup table
search, or something ... read the fmgr code for details).

			regards, tom lane

Sat, 13 Jul 2002 21:01:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: question re internal functions requiring initdb 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>It made me wonder why don't we always create internal functions this 
>>way, or at least all except a core set of bootstrapped functions.
> 
> I don't believe it will actually work: you *must* add an internal
> function to include/catalog/pg_proc.h, or it won't get into the function
> lookup table that's built by Gen_fmgrtab.sh.
> 
> It is true that you don't have to force an initdb right away, but
> there's an efficiency penalty IIRC (can't bypass the lookup table
> search, or something ... read the fmgr code for details).
> 

OK -- I see what you mean now. For a *user alias* of an existing builtin 
function fmgr_isbuiltin(), which does a binary search on the sorted 
fmgr_builtins array, will fail. So there is a speed penalty in that the 
function is looked up with fmgr_lookupByName(), which does a sequential 
scan through the fmgr_builtins array.

Regardless, if the function is not listed in the fmgr_builtins array at 
all, which it won't be if Gen_fmgrtab.sh doesn't see it in pg_proc.h, 
then the lookup will fail entirely. I guess I would have found this out 
on my own if I had carried the experiment out a little farther. Shucks!

Joe


Sat, 13 Jul 2002 19:25:53 -0700
Joe Conway <mail@joeconway.com>
Re: question re internal functions requiring initdb