CODEWITHSUNDEEP
sqlpostgresql-9.6
Martin
Martin

Reputation: 9954

postgresql - determine schema from table name

I have multiple schemas each containing a table with the same name "t".

How do I find out what schema "select * from t" is evaluated in ?

Example:

create schema one;
create schema two;
create schema three;
create table one.t(i int);
create table two.t(i int);

set search_path to one,two;
select magic_function('t');  -- returns 'one'

set search_path to two,one;
select magic_function('t');  -- returns 'two'

set search_path to three,two,one;
select magic_function('t');  -- returns 'two'

What is "magic_function" in this case ?

Upvotes: 1

Views: 393

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

you dont need fn() here - just plain select:

t=# set search_path to one,two;
SET
t=# select relname, relnamespace::regnamespace from pg_class where oid = 't'::regclass;
 relname | relnamespace
---------+--------------
 t       | one
(1 row)

t=# set search_path to two,one;
SET
t=# select relname, relnamespace::regnamespace from pg_class where oid = 't'::regclass;
 relname | relnamespace
---------+--------------
 t       | two
(1 row)

of course it's wrappable if you demand

Upvotes: 2

Related Questions