Re: Exposed function to find table in schema search list?

I'm writing a function that accepts a table name and digs some information
out about it. I'm developing on 7.3cvs w/schemas, and wanted my function to
use schemas.

If the user gives a full schema name (s.table), I find the table in pg_class
by comparing the ns oid and relname.

However, if the user doesn't give the full schema name, I have to find which
table they're looking for by examining current_schemas, iterating over each
schema in this path, and trying it.

Is there a function already in the backend to return a class oid, given a
name, by looking up the table in the current_schemas path? Would it make
sense for us to expose this, or write one, so that this small wheel doesn't
have to be re-invented everytime someone wants to find a table by just the
name?

Something like:

findtable(text) returns oid
findtable("foo") -> oid of foo (given current search path)
findtable("s.foo") -> oid of s.foo

I can write something in plpgsql (iterating over the array, checking each,
etc.), however, it would be nice if something was already there.

Any ideas?

Thanks!

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

Joel Burton wrote:
> Is there a function already in the backend to return a class oid, given a
> name, by looking up the table in the current_schemas path? Would it make
> sense for us to expose this, or write one, so that this small wheel doesn't
> have to be re-invented everytime someone wants to find a table by just the
> name?
>
> Something like:
>
> findtable(text) returns oid
> findtable("foo") -> oid of foo (given current search path)
> findtable("s.foo") -> oid of s.foo
>
> I can write something in plpgsql (iterating over the array, checking each,
> etc.), however, it would be nice if something was already there.

I think this already exists:

test=# select 'joe.foo'::regclass::oid;
oid
--------
125532
(1 row)

test=# select 'public.foo'::regclass::oid;
oid
--------
125475
(1 row)

test=# select 'foo'::regclass::oid;
oid
--------
125475
(1 row)

test=# select current_schema();
current_schema
----------------
public
(1 row)

Joe

> -----Original Message-----
> From: Joe Conway [mailto:mail(at)joeconway(dot)com]
> Sent: Sunday, May 19, 2002 5:25 PM
> To: Joel Burton
> Cc: Pgsql-Hackers(at)Postgresql(dot) Org
> Subject: Re: [HACKERS] Exposed function to find table in schema search
> list?
>
>
> Joel Burton wrote:
> > Is there a function already in the backend to return a class
> oid, given a
> > name, by looking up the table in the current_schemas path? Would it make
> > sense for us to expose this, or write one, so that this small
> wheel doesn't
> > have to be re-invented everytime someone wants to find a table
> by just the
> > name?
> >
> > Something like:
> >
> > findtable(text) returns oid
> > findtable("foo") -> oid of foo (given current search path)
> > findtable("s.foo") -> oid of s.foo
> >
> > I can write something in plpgsql (iterating over the array,
> checking each,
> > etc.), however, it would be nice if something was already there.
>
> I think this already exists:
>
> test=# select 'joe.foo'::regclass::oid;
> oid
> --------
> 125532
> (1 row)
>
> test=# select 'public.foo'::regclass::oid;
> oid
> --------
> 125475
> (1 row)
>
> test=# select 'foo'::regclass::oid;
> oid
> --------
> 125475
> (1 row)
>
> test=# select current_schema();
> current_schema
> ----------------
> public
> (1 row)

Perfect! I was hoping to avoid re-creating the wheel. Thanks, Joe.

Is the use of regclass going to prove to be very implementation-specific?
Would it make sense for us to create a function that abstracts this?

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

"Joel Burton" <joel(at)joelburton(dot)com> writes:
> Is the use of regclass going to prove to be very
> implementation-specific?

Sure, but so would any other API for it.

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Sunday, May 19, 2002 6:40 PM
> To: Joel Burton
> Cc: Joe Conway; Pgsql-Hackers(at)Postgresql(dot) Org
> Subject: Re: [HACKERS] Exposed function to find table in schema search
> list?
>
>
> "Joel Burton" <joel(at)joelburton(dot)com> writes:
> > Is the use of regclass going to prove to be very
> > implementation-specific?
>
> Sure, but so would any other API for it.

Well, sort of, but if we had been promoting a function tableoid(text)
returns oid, we wouldn't have to make any change for the move to regclass,
would we? I mean, it's specific to PG, but a simple wrapper might outlive
the next under-the-hood change.

On a related note: is there an easy way to use this ::regclass conversion to
test if a table exists in a non-error returning way? (Can I use it in a
select statement, for instance, returning a true or false value for the
existence or non-existence of a table?)

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

"Joel Burton" <joel(at)joelburton(dot)com> writes:
> Well, sort of, but if we had been promoting a function tableoid(text)
> returns oid, we wouldn't have to make any change for the move to regclass,
> would we? I mean, it's specific to PG, but a simple wrapper might outlive
> the next under-the-hood change.

I think you miss the point: regclass is that wrapper. tableoid(text)
is only syntactically different --- and for that matter there's nothing
stopping you from writing regclass('tablename').

> On a related note: is there an easy way to use this ::regclass conversion to
> test if a table exists in a non-error returning way? (Can I use it in a
> select statement, for instance, returning a true or false value for the
> existence or non-existence of a table?)

At the moment regclass conversion raises an error if the item isn't
found; this follows the historical behavior of regproc. We could
possibly have it return 0 (InvalidOid) instead, but I'm not convinced
that that's better. In the case of regproc, not erroring out would
lose some important error checking during initdb.

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, May 24, 2002 1:33 PM
> To: Joel Burton
> Cc: Pgsql-Hackers(at)Postgresql(dot) Org
> Subject: Re: [HACKERS] Exposed function to find table in schema search
> list?
>
> At the moment regclass conversion raises an error if the item isn't
> found; this follows the historical behavior of regproc. We could
> possibly have it return 0 (InvalidOid) instead, but I'm not convinced
> that that's better. In the case of regproc, not erroring out would
> lose some important error checking during initdb.

Fair enough. Is there any way to handle this error and return a false?
(People frequently ask "how can I check to see if a table exists", and not
all interfaces handle errors the same way, but everyone should know how to
deal with a table result, so that we can provide a 7.3 version of "SELECT 1
FROM pg_class where relname='xxx'".

Thanks!

- J

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

"Joel Burton" <joel(at)joelburton(dot)com> writes:
>> At the moment regclass conversion raises an error if the item isn't
>> found; this follows the historical behavior of regproc. We could
>> possibly have it return 0 (InvalidOid) instead, but I'm not convinced
>> that that's better. In the case of regproc, not erroring out would
>> lose some important error checking during initdb.

> Fair enough. Is there any way to handle this error and return a false?
> (People frequently ask "how can I check to see if a table exists", and not
> all interfaces handle errors the same way, but everyone should know how to
> deal with a table result, so that we can provide a 7.3 version of "SELECT 1
> FROM pg_class where relname='xxx'".

Well, I have no strong objection to providing an alternate API that
does things that way. I was thinking the other day that we need
text-to-regclass, regclass-to-text, etc conversion functions (these
don't come for free given the I/O routines, sadly enough). Perhaps we
could define, say, text-to-regclass to return NULL instead of throwing
an error on bad input.

This might be a tad awkward to use though, since you'd have to write
something like
'nosuchtable'::text::regclass
or
regclass('nosuchtable'::text)
to get a literal parsed that way rather than fed directly to the
regclass input converter (which we're assuming will still raise an
error).

As far as I'm concerned none of this is set in stone, so I'm open to
better ideas if anyone's got one ...

regards, tom lane