Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dealing with table names in functions

Dealing with table names in functions

From
Jim Nasby
Date:
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.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Re: Dealing with table names in functions

From
"Merlin Moncure"
Date:
On 3/22/07, Jim Nasby <decibel@decibel.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

Re: Dealing with table names in functions

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Is there a safe way to deal with tables being passed into a function,
> specifically in terms of what schema they're in?

Pass in the schema and tablename together as a string:
select foobar('public.baz');

or (better, IMO) make it two separate arguments:

select foobar('baz', 'public');

I usually put the table first as it allows me to overload the function
with a single arg and a default schema.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200703220923
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFGAoOGvJuQZxSWSsgRAxYOAJ9kuyz8YY+LvMsVxHSuqFbintcSAQCfWX6y
zk5PVMhN9Pqxxkwvy/erCbw=
=ZTzZ
-----END PGP SIGNATURE-----

Re: Dealing with table names in functions

From
Alvaro Herrera
Date:
Jim Nasby 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.

Pass the optionally qualified name and cast it to regclass.  It will
work correctly when the name is not qualified, applying search_path, and
it will also work when the name is qualified.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Dealing with table names in functions

From
Tom Lane
Date:
Jim Nasby <decibel@decibel.org> writes:
> ... I can cast the OID to regclass, but that
> doesn't get me a fully-qualified name.

It does if the name needs to be qualified given your current search_path.

            regards, tom lane

Re: Dealing with table names in functions

From
Reece Hart
Date:
On Thu, 2007-03-22 at 09:40 -0400, Alvaro Herrera wrote:
> Pass the optionally qualified name and cast it to regclass.  It will
> work correctly when the name is not qualified, applying search_path,
> and it will also work when the name is qualified.

Is there a way to get names that are always qualified, irrespective of
the search_path? Once one has the oid, it's an easy function to write,
but I suspect this machinery already exists.

For example, I'd like the hypothetical cast:
        rkh@csb-dev=> set search_path = 'unison';
        SET

        rkh@csb-dev=> select 'pseq'::regclass::oid::FQregclass;
         regclass
        ----------
         unison.pseq
        (1 row)


-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.