CODEWITHSUNDEEP
postgresql
exodehm
exodehm

Reputation: 581

Check if a custom type is installed in a schema

I know that I can make a query in pg_type table for check if any type exists, like that:

SELECT 1 FROM pg_type WHERE typname = 'mycustomtype'

But if this customtype exists in other schema different to the schema I want to install it, I´ll get a true, and then a script like that won´t work:

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
        CREATE TYPE task_status AS ENUM ('todo', 'doing', 'blocked', 'done');
    END IF;
END
$$;

I want to use this script into the scripts of an extension, but it can fail if there are a type with the same name but in other schema

Upvotes: 1

Views: 384

Answers (1)

user330315
user330315

Reputation:

You can include the schema name in your WHERE condition:

SELECT true
FROM pg_type 
WHERE typname = 'mycustomtype'
  and typnamespace = 'your_schema'::regnamespace

I want to use this script into the scripts of an extension

You should set the search_path in your extension's script if you want to make your extension relocatable

In that case you probably should use:

SELECT true
FROM pg_type 
WHERE typname = 'mycustomtype'
  and typnamespace = '@extschema@'::regnamespace

To check the existence in the schema specified during create extension

Upvotes: 3

Related Questions