What is the difference between SPECIFIC_SCHEMA and ROUTINE_SCHEMA in INFORMATION_SCHEMA.ROUTINES?

Question

INFORMATION_SCHEMA.ROUTINES contains these two columns:

SPECIFIC_SCHEMA: Specific name of the schema.
ROUTINE_SCHEMA: Name of the schema that contains this function.

technet doc

For the SPECIFIC_ and ROUTINE_ versions of CATALOG and NAME are defined to be equivalent (-Specific name of the catalog. This name is the same as ROUTINE_CATALOG.-), but this verbiage is omitted for SCHEMA-

What is the difference between SPECIFIC_SCHEMA and ROUTINE_SCHEMA?

[Edit: Apparently 3+ years later this is being flagged as a duplicate so I have to clarify. The suggested duplicate is about different fields, so no I do not beleive it is a duplicate.]

Answer 1

This is for overloading. And the question is a duplicate of this question here.

Essentially, you can overload a function in just one schema in the SQL spec.

Overloading and lookup.

Let's take for instance ST_Union, which you can see documented with signatures here, and here

SELECT specific_catalog, specific_schema, specific_name, routine_catalog, routine_schema, routine_name
FROM information_schema.routines
WHERE routine_name = 'st_union';
 specific_catalog | specific_schema |  specific_name  | routine_catalog | routine_schema | routine_name 
------------------+-----------------+-----------------+-----------------+----------------+--------------
 test             | public          | st_union_259584 | test            | public         | st_union
 test             | public          | st_union_259621 | test            | public         | st_union
 test             | public          | st_union_259622 | test            | public         | st_union
 test             | public          | st_union_260448 | test            | public         | st_union
 test             | public          | st_union_260450 | test            | public         | st_union
 test             | public          | st_union_260452 | test            | public         | st_union
 test             | public          | st_union_260454 | test            | public         | st_union
 test             | public          | st_union_260456 | test            | public         | st_union
(8 rows)

You can see here that st_union is overloaded 8 different times. What you can't see is why: some of them are aggregates, some of them accept rasters and others geometry. Point is, you call st_union depending on what you call it with you may get st_union_260454 or st_union_259621, or any of the others.

Catch is, SQL permits you to get a different one depending on the schema you call it in.

SELECT current_schema;

That returns public. That's the first schema in my search_path. I can create a sub that provides a 9th ST_Union, but one that only works inside the schema foobar. Doing so would provide the 9th overload of routine_name st_union. That would make the above return something like this...

SELECT specific_catalog, specific_schema, specific_name, routine_catalog, routine_schema, routine_name
FROM information_schema.routines
WHERE routine_name = 'st_union';
 specific_catalog | specific_schema |  specific_name  | routine_catalog | routine_schema | routine_name 
------------------+-----------------+-----------------+-----------------+----------------+--------------
 test             | public          | st_union_259584 | test            | public         | st_union
 test             | public          | st_union_259621 | test            | public         | st_union
 test             | public          | st_union_259622 | test            | public         | st_union
 test             | public          | st_union_260448 | test            | public         | st_union
 test             | public          | st_union_260450 | test            | public         | st_union
 test             | public          | st_union_260452 | test            | public         | st_union
 test             | public          | st_union_260454 | test            | public         | st_union
 test             | public          | st_union_270456 | test            | foobar         | st_union
(9 rows)

• If my search_path is just foobar then the signature better match the types of st_union_270456
• If my search_path is foobar,public then either foobar is overriding a routine in public or providing a routine public is not providing.

CURRENT_SCHEMA is just the first schema in search_path for PostgreSQL. PostgreSQL extends upon the spec by allowing you to resolve things in multiple schema.

Answer 2

Well it is a bit like a backwards compatibility.

The INFORMATION_SCHEMA itself is just used for easier migrations of Server versions and simplification. I will provide the examples based on SQL Server.

You may want (or maybe need) to use the table sys.objects, which basically provides nearly all the same information which were delivered by INFORMATION_SCHEMA.ROUTINES. In fact you need to use it, if you really want to know the real schema name for a procedure - as Microsoft already state in their own docs. But if you need to build a product which just want some basic information like if a routine exists or something like that, you won't go and take sys.objects it may change from server version to server version. Which makes it harder to maintain. The INFORMATION_SCHEMA basically stays the same over many versions. Only small changes will be applied and the work beneath the view, will be changed by Microsoft. This means you have a stable code for many years and many different server versions.

This may be the fact, that there are two columns with the same definition and therefore same content. It's the same like SPECIFIC_NAME and ROUTINE_NAME or SPECIFIC_CATALOG and ROUTINE_CATALOG. They are basically the same, as Microsoft self stated in their docs.

It could be a relic, which may stay there for ever, just for the compatibility t other DBMS code or older versions of SQL Server itself.

Just by the way, Postgres have the same two rows which both contains the same content.