Thread: How to get tabloid
List, I have a temporary table and I need to get the tableoid from the interface. The table may not have any data in it so something like SELECT tableoid FROM tmp_table LIMIT 1, returns zero when there is no data in the table. I know that you can get information like this from the system catalogs but don't know how to join them to accomplish this. Is there a diagram available which shows the system catalog relationships and does anyone have a simple sql string that will give me the tableoid of a given table. TIA --
To answer at least part of my own question: SELECT oid FROM pg_class WHERE relname = 'cust'; apparently gives the tableoid. On Friday 30 September 2005 06:25 am, Terry Lee Tucker saith: > List, > > I have a temporary table and I need to get the tableoid from the interface. > The table may not have any data in it so something like SELECT tableoid > FROM tmp_table LIMIT 1, returns zero when there is no data in the table. I > know that you can get information like this from the system catalogs but > don't know how to join them to accomplish this. Is there a diagram > available which shows the system catalog relationships and does anyone have > a simple sql string that will give me the tableoid of a given table. > > TIA > > -- > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org --
On Fri, Sep 30, 2005 at 06:53:25AM -0400, Terry Lee Tucker wrote: > To answer at least part of my own question: > > SELECT oid FROM pg_class WHERE relname = 'cust'; > > apparently gives the tableoid. This query should restrict its results with pg_table_is_visible() -- otherwise you might get the wrong oid if the temporary table has the same name as a non-temporary table, or if multiple sessions have temporary tables with the same name. Here's another way: SELECT 'cust'::regclass::oid; -- Michael Fuhr
Thank you Michael. I did see the behavior you mentioned when two sessions had temp tables with the same name. Your way is much better. On Friday 30 September 2005 01:30 pm, Michael Fuhr saith: > On Fri, Sep 30, 2005 at 06:53:25AM -0400, Terry Lee Tucker wrote: > > To answer at least part of my own question: > > > > SELECT oid FROM pg_class WHERE relname = 'cust'; > > > > apparently gives the tableoid. > > This query should restrict its results with pg_table_is_visible() -- > otherwise you might get the wrong oid if the temporary table has > the same name as a non-temporary table, or if multiple sessions > have temporary tables with the same name. > > Here's another way: > > SELECT 'cust'::regclass::oid; > > -- > Michael Fuhr --