CODEWITHSUNDEEP
postgresqlselect
Santhos
Santhos

Reputation: 3498

Postgres: variable saying from which schema I select

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

Upvotes: 1

Views: 1800

Answers (2)

dbenhur
dbenhur

Reputation: 20408

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';

Upvotes: 0

user330315
user330315

Reputation:

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

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

Upvotes: 4

Related Questions