Dealing with table names in functions

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

-----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-----

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.

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

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.