Re: Some questions on user defined types and functions.

Jeffery Collins wrote:

> I was wondering if anyone could help me with the following questions.
> They are all related to user defined types and functions.
>
>     1. Environment variables in function pathname.  We would like to
> have multiple environments (i.e. production, backup, reporting, test
> etc) with each environment totally independent of the other
> environments.  One thing that stands in the way of this is the absolute
> pathname requirement in the CREATE FUNCTION syntax.  Obtimally we would
> like the following syntax to work:
>
>             CREATE FUNCTION myfunc(mytype) RETURNS text AS
> '$HOME/lib/libmyso.so' LANGUAGE 'c':
>
>         and have the environment variable $HOME "lazy" evaluated.  I
> have looked at the fmgr code and this doesn't look too difficult to add
> as long as I could get the $HOME past the parser.  Has anyone thought
> about this before?  Is there another, better way to do this?
>
>     2. tid assignment - We make extensive use of user defined types.
> One, fairly painful, thing we have noticed is the following sequence
> really doesn't work very well:
>
>             CREATE FUNCTION mytype_in(opaque) RETURNS mytype AS
> '/lib/libmyso.so' LANGUAGE 'c';
>             CREATE FUNCTION mytype_out(opaque) RETURNS opaque AS
> '/lib/libmyso.so' LANGUAGE 'c';
>             CREATE TYPE mytype (internallength = VARIABLE,
> input=mytype_in, output=mytype_out);
>             CREATE TABLE mytable (t mytype);
>             DROP TYPE mytype;
>             then create the type again
>
>         The reason for possibly wanting to do this is to fix a problem
> with the implementation of the type mytype.  The reason this doesn't
> seem to work is that the definition of mytable "knows" the TypeID of
> mytype and, because it got dropped and recreated, the TypeID is now
> different.  So the question is, is there a way to modify the definition
> of a type without dropping and recreating all of the tables that use the
> type.
>
>     3. fid assignment - Basically the same question as above but with
> functions instead of types.  If there is an index that uses a function,
> it appears that you can't drop and re-create the function without
> blowing away the index definition.
>

Never mind on questions 2 and 3.  I figured out the answer.  It seems the
way to do this is to update the system tables (pg_attributes, pg_index,
etc.) to refer to the new oids instead of old oids.

I would still appreciate any thoughts on using environment variables in
function paths.

>
> Thank you,
> Jeff Collins