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