[CT-3263] [Feature] User space Jinja macro for converting DB API 2.0 `type_code`s into `data_type`s

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [X] I have searched the existing issues, and I could not find an existing issue for this feature
- [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

Different data platforms may have specialized or non-started data types (think `MONEY`, `UUID`, `GEOMETRY`, `GEOGRAPHY`, etc.) This will cause issues when the `type_code` from the associated Python database driver is unrecognized.

See below for a prototype that allows converting an unrecognized `type_code` into the desired `data_type`.

### Example prototype

https://github.com/dbt-labs/dbt-core/pull/8887#issuecomment-1779331994

This prototype can be implemented directly by users. Alternatively, it could be re-written in such a way that it could be customized by adapter maintainers as-needed.

### Decision criteria for where adapters-related logic should live

From @MichelleArk [here](https://github.com/dbt-labs/dbt-core/pull/8887#issuecomment-1779475120):

> In case it's helpful, my decision criteria for where adapters-related logic should live boils down to:
> 1. If the implementation does not or should not vary by adapter -> dbt-core
> 2. If it's adapter specific + has _only one objectively correct implementation_ -> adapter interface, implemented in python for maintainers to overwrite as necessary.
> * This is also easier to unit test for adapter maintainers!
> 3. If it's adapter specific + should be flexible to end-user tweaks and customizations -> adapter interface, implemented in jinja (user space) for end-users to customize entirely how they see fit for their dbt project.
>
> As much as I'd like this mapping of `type_code` to `data_type` to have a single 'correct' implementation per adapter, I can see how user-defined types put a bit of a wrench in that. It may be possible to split the implementation into base types in python (could follow a similar pattern to https://github.com/dbt-labs/dbt-core/pull/8357) and user-defined types, but that may introduce more complexity than it's worth...

### One possible approach (to be refined)
1. Create an adapter-specific **base** lookup table of `type_code` → `data_type` (within Python or Jinja)
1. Allow an adapter-specific **supplemental** lookup table of `type_code` → `data_type` (in user-space Jinja)
1. Create a dbt-core implementation that combines 1) and 2) above (with the latter taking precedence)
1. Use 3) above within adapter-specific `data_type_code_to_name` (Python) implementations (see [here](https://github.com/dbt-labs/dbt-core/blob/ef9d6a870f3cc864ba3240d393a1a9a5b1de529d/core/dbt/adapters/base/connections.py#L430) and [here](https://github.com/dbt-labs/dbt-core/blob/ef9d6a870f3cc864ba3240d393a1a9a5b1de529d/plugins/postgres/dbt/adapters/postgres/connections.py#L206))

### Alternative approach 1
1. As above
1. As above
1. Update [`get_column_schema_from_query`](https://github.com/dbt-labs/dbt-core/blob/ef9d6a870f3cc864ba3240d393a1a9a5b1de529d/core/dbt/adapters/base/impl.py#L323) to take 1) and 2) into account (with the latter taking precedence)

### Alternative approaches 2 and 3
Same as the two approaches above, but with only a single adapter-specific lookup table (instead of separate base and supplemental lookup tables)

I haven't considered any of the above options deeply enough to have a preference yet.

### Describe alternatives you've considered

The main alternative is doing these lookups with user-provided (or package-provided) macros.

The example prototype above can be entirely done by users without there being any interfaces added to dbt-core or adapters.

These macros can be defined in two places:
1. `macros` directory of the user's dbt project
2. within a dbt package installed by the user

It would be easier if dbt can provide base implementations and documentation though.

### Who will this benefit?

A primary use-case is being able to properly display the actual/expected data types when contract enforcement fails (c.f. https://github.com/dbt-labs/dbt-core/pull/8887).

A two-fer-one use-case is direct usage of `get_column_schema_from_query` (like described in https://github.com/dbt-labs/dbt-core/issues/8877) so that [`Column.data_type`](https://docs.getdbt.com/reference/dbt-classes#column) contains legit data types.

This will benefit users of dbt-postgres that have data types like `money`, `uuid`, etc that don't automatically have a `type_code` lookup within `psycopg2`. It also would cover the case of `geometry` within the postgis distribution of postgres as well as user-defined types.

There might be other adapters besides dbt-postgres that have similar needs.

### Are you interested in contributing this feature?

_No response_

### Anything else?

_No response_