Postgres: variable saying from which schema I select

Question

I have created a SQL SELECT to get all enums and their values in a schema:

SELECT 
    t.typname, array_agg(e.enumlabel) 
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE t.typtype = 'e' AND n.nspname = 'public'
GROUP BY typname

I have put the select into a view so I dont have to write it everytime I want to call it. The only thing that bothers me is that if I rename the schema or use it in another schema I have to rewrite the name of the schema manually, check line 6 of the code:

    WHERE t.typtype = 'e' AND n.nspname = 'public'

Public is hardtyped there. When selecting in postgres, is there a "global" variable saying from which schema you select? I was not able to find any.

Thanks

PS: I use postgres 8.4

user330315 · Accepted Answer · 2012-04-10 14:57:20Z

4

The current schema can be retrieved using the function current_schema()

http://www.postgresql.org/docs/current/static/functions-info.html

answered Apr 10, 2012 at 14:57

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Santhos Over a year ago

Thank you, that is exactly what I was looking for and thanks for correcting the spelling :) I am always confused whether to use Postgre or Postgres

kgrittn Over a year ago

Well, officially it is PostgreSQL, but Postgres is a popular nickname. Even many of those who use the official spelling pronounce it like the shorter form. (I do.) wiki.postgresql.org/wiki/…

user330315 Over a year ago

@Santhos: In addtion to kgrittn's comment: Postgre (without the s) is never right

dbenhur · Accepted Answer · 2012-04-10 22:13:10Z

0

Alternatively to wiring the schema or looking it up with current_schema(), you could make your view group by schema as well and then select on the view.

create or replace view enum_vw as
SELECT 
    n.nspname, t.typname, array_agg(e.enumlabel) 
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE t.typtype = 'e'
GROUP BY n.nspname, t.typname;

select * from enum_vw where nspname = 'public';

answered Apr 10, 2012 at 22:13

dbenhur

20.5k4 gold badges52 silver badges47 bronze badges

1 Comment

Santhos Over a year ago

That would work too, but I want a clean look: enum_name => enum_values. The name of the namespace does not interest me much. I only need to make it work under every schema, that is why current_schema() is the thing i was looking for. Anyway, I appreciate your asnwer. Thanks

Collectives™ on Stack Overflow

Postgres: variable saying from which schema I select

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related