Re: Dealing with table names in functions

On 3/22/07, Jim Nasby <decibel(at)decibel(dot)org> wrote:
> Is there a safe way to deal with tables being passed into a function,
> specifically in terms of what schema they're in? I can just blindly
> accept a text string and hope that it's always evaluated in the
> correct search_path context, but that doesn't seem so good. OTOH, if
> I accept an OID, there's no great way to pass that to most of the
> rest of the system... I can cast the OID to regclass, but that
> doesn't get me a fully-qualified name.
>
> It would be nice if there was a way to convert an OID into a fully-
> qualified name.
>
> I'm working on some partitioning stuff, and I'm currently writing a
> function that will return the name of a partition given the parent
> table and what period to partition on (ie: day, month, year, etc).
>
> Originally, I thought I'd just accept an OID for the table name, but
> I can't think of a safe way to look up that tables schema name
> (because I want to return a fully qualified name). Obviously, I can
> look in pg_class and pg_namespace, but someone could do a DROP TABLE
> between when I do that lookup and when I actually use the name. So I
> thought I'd just do a LOCK TABLE... except I need the table name to
> do that. Catch-22.

You can select pg_class name in transaction by oid 'for update'. This
will block drop table, etc. from other sessions. This will only work
if you are the superuser however.

merlin