1

I am trying to create a trigger that uses ltree column.

CREATE EXTENSION IF NOT EXISTS ltree;

Running

SELECT * FROM pg_catalog.pg_extension 

gives the following output:

extname     |extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
------------|--------|------------|--------------|----------|---------|------------|
plpgsql     |      10|          11|false         |1.0       |NULL     |NULL        |
ltree       |      10|      361964|true          |1.1       |NULL     |NULL        |
postgres_fdw|      10|      361970|true          |1.0       |NULL     |NULL        |

I cannot drop the extension because I get an error that there are dependencies. I was also able to create a table that has a column with data type ltree without an error.

When trying to create a trigger, I get the following error:

SQL Error [42704]: ERROR: type "ltree" does not exist
  Position: 252

This is a snippet of the trigger and the problem seems to be happeing when I declare a variable of type ltree:

CREATE OR REPLACE FUNCTION asset.update_asset_path()

RETURNS TRIGGER

AS $$
    DECLARE
        db_path_ltree ltree;
        single_asset INT;
        s3path TEXT = '';
        ltree_array TEXT[];
        v_asset_name TEXT;
        i_last_element INT;

What is confusing, is this is only happening in one environment for dev. Every other postgres server does not have this problem and is using the same code.

Any ideas why this is happening?

0

The extension ltree is installed in the schema with the object ID 361964. If you want to know the name of that schema, run

SELECT 361964::regnamespace;

It could be that that schema is not on the search_path when you execute the function. That would explain the error.

If you install an extension in a schema other than public, it is usually best to reference its objects with schema qualification, for example

DECLARE
    db_path_ltree extschema.ltree;
  • Thanks for clearing that up. We use flyway to clean and migrate databases, which automatically created extension in flyway schema. Whereas we typically create extensions in public – kravb Dec 5 '19 at 17:33
  • There is the SCHEMA clause in CREATE EXTENSION that gives you control. – Laurenz Albe Dec 6 '19 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.