2

I'm trying to run an inline query on my database - which has the citext extension installed (using CREATE EXTENSION) - and yet the executed query keeps throwing this error when calling a function:

type "citext" does not exist 

DO
LANGUAGE plpgsql
$$
DECLARE
  _id INT;
BEGIN
  SELECT * FROM "dbo"."MyFunction"(_id, 'some value'::citext);
END;
$$;

If I omit the ::citext cast, it says:

function dbo.MyFunction(integer, unknown) does not exist.
You might need to add explicit type casts.

The citext extension is added, is part of the schema and works with other queries. This keeps coming up randomly - what causes it?

EDIT: The installed extensions:

extname   | nspname
----------+-----------
plpgsql   | pg_catalog
citext    | public
uuid-ossp | public

Search path:

show search_path;
search_path
-----------
dbo
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Michael Brown
  • 1,585
  • 1
  • 22
  • 36
  • `citext ... is part of the schema` - part of which schema? Run: `SELECT e.extname, n.nspname FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace;` And what do you get for `show search_path`? – Erwin Brandstetter Sep 01 '16 at 02:37
  • updated question to reflect that information. Interesting to note that I also seem to have this problem trying to use uuid_generate_v4() in an inline query. – Michael Brown Sep 01 '16 at 03:03
  • oh man.. this might be specific to DataGrip 2016 and the way it initiates connections. I tried the same query with pgAdmin and the search_path contains the extensions, whereas it does not in DataGrip. – Michael Brown Sep 01 '16 at 03:09
  • Unrelated but: you should really avoid quoted identifiers. They are much more trouble then they are worth it. –  Sep 01 '16 at 11:21
  • So it is as suspected. I added some more pointers to my answer. – Erwin Brandstetter Sep 01 '16 at 11:33

1 Answers1

2

As suspected, the extension schema is missing from the search_path. Read up on how to set the schema search path in this related answer:

It seems like your client sets search_path = dbo on connection, which seems to be misconfigured. dbo is something we see a lot for SQL Server (used to be the default schema here or still is?), very untypical for Postgres. Not sure how you got there.

One alternative would be to install extensions into the dbo schema as well:

You can even move (most) extensions to a different schema:

ALTER EXTENSION citext SET SCHEMA dbo;

But I would advice to install extensions to a dedicated schema and include it in the search_path.

Leave plpgsql alone in any case. It's installed by default and should should stay in pg_catalog.

One way or another, clean up the mess with varying search_path settings.

As for the second question: that's guided by the rules of Function Type Resolution. The call cannot be resolved, because citext does not have an implicit cast to text.

Related

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228