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?
Best Answer
The extension
ltree
is installed in the schema with the object ID 361964. If you want to know the name of that schema, runIt 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