Way to identify the current session's temp tables within pg_class ?

On Mon, Aug 25, 2014 at 7:48 PM, Marc Mamin <M.Mamin@intershop.de> wrote:

Hello,

When different sessions create temp tables with the same name:
How can I identify the oid of the one created within the current session ?

You can use pg_my_temp_schema for this purpose. It returns the OID of
the schema where temporary objects are stored for a given session.
Note that this returns InvalidOid if no temporary objects are defined.
Here is an example:
=# select pg_my_temp_schema();
pg_my_temp_schema
-------------------
0
(1 row)
=# create temp table aa (a int);
CREATE TABLE
=# SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
nspname
-----------
pg_temp_4
(1 row)

Regards,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----Original Message-----
From: Michael Paquier [mailto:michael.paquier@gmail.com]
On Mon, Aug 25, 2014 at 7:48 PM, Marc Mamin <M.Mamin@intershop.de>
wrote:

Hello,

When different sessions create temp tables with the same name:
How can I identify the oid of the one created within the current

session ?

You can use pg_my_temp_schema for this purpose. It returns the OID of
the schema where temporary objects are stored for a given session.
Note that this returns InvalidOid if no temporary objects are defined.

thanks,

I've also notice that ::regclass only returns the oid of the "accessible" table.
It is sufficient for my need, although it doesn't tell whether the table is temporary or not:

SELECT * FROM pg_class where oid='foo'::regclass

regards,

marc

Here is an example:
=# select pg_my_temp_schema();
pg_my_temp_schema
-------------------
0
(1 row)
=# create temp table aa (a int);
CREATE TABLE
=# SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
nspname
-----------
pg_temp_4
(1 row)

Regards,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general