Re: Some questions on user defined types and functions.

Tom Lane wrote:

> Jeffery Collins <collins(at)onyx-technologies(dot)com> writes:
> > 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.
>
> The parser doesn't know a thing about that, it's just seeing a string
> literal. I think hacking in dfmgr.c would be sufficient. Whether it's
> a good idea is another question --- you realize you'd be dealing with
> postmaster environment variables, right, not those of the connected
> user? The way we handle this in the distribution is by substituting
> appropriate strings into a script before it's handed to psql; see the
> regression tests directory for examples.
>

Yes, I want the backend's environment to be used, so this is what I want.
It looks like a pretty simple change. I'm going to give it a shot. Whether
or not it is something y'all want for the main distribution is, of course,
up to you.

>
> > 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.
>
> I think it would be a really bad idea to allow recycling of type and
> function OIDs for what might be completely incompatible objects. But
> something that's been on the TODO list for a while is to create an ALTER
> FUNCTION command that would replace the body of an existing function
> without changing the declared signature (parameters and return type).
> That seems relatively safe, and it'd be awfully handy. Want to have a
> go at it?
>
> regards, tom lane
>
> PS: you do realize that revising the function on which an index is
> based probably renders the index useless anyway? Unless you can
> guarantee that none of the stored values change...

I agree it is a really bad idea, but sometimes bad things happen to good
databases. I am really just attempting to figure out how to recover if
someone accidently types a DROP TABLE or DROP FUNCTION command or (as
actually did happen) needs to change the function's path. Assuming the
reCREATEd TABLE and/or FUNCTION matches the old ones, it looks like updating
the oid references in the relevant system tables to point to the new TABLE
and/or FUNCTION oids works just fine.

As far as looking at the ALTER FUNCTION command, I can't promise anything (I
have a lot of work commitments and a vacation coming up), but I will take a
look at it.

Thank you,
Jeff