Can I create a cast from a composite (table or row) type to a domain?

Question

Let's say I have a simple table foo, and domain bar

CREATE TABLE foo(x)
AS VALUES (1),(2),(3);

CREATE DOMAIN bar AS text;

Is it possible to make a cast such that I can do

SELECT foo::bar FROM foo;

To start this, I create a function which takes a foo to a bar,

CREATE FUNCTION foo_to_bar(t foo)
RETURNS bar
AS $$
  SELECT FORMAT('%s AS BAR', t.x)::bar
$$
LANGUAGE SQL
IMMUTABLE;

I can see this works,

SELECT foo_to_bar(foo), pg_typeof(foo_to_bar(foo)) FROM foo;
 foo_to_bar | pg_typeof 
------------+-----------
 1 AS BAR   | bar
 2 AS BAR   | bar
 3 AS BAR   | bar

But when I do,

CREATE CAST (foo as bar) WITH FUNCTION foo_to_bar;

I get this weird warning,

WARNING: cast will be ignored because the target data type is a domain

And the result is even more bizzare,

$ SELECT foo::bar, pg_typeof(foo::bar) FROM foo;
 foo | pg_typeof 
-----+-----------
 (1) | bar
 (2) | bar
 (3) | bar
(3 rows)

You can see that we're returning the right type, but the value isn't what I would expect. I would expect this to produce the same value as the above where I use foo_to_bar.

• Is it possible to cast to a domain type?
• If no, what does the above do? Why do I get (1), (2), and (3)?

Answer 1

You'll get the (1) result even without creating the cast or the function. That's simply the notation for the whole row from foo when treated as a record.

CREATE TABLE foo(x) AS VALUES (1),(2),(3);
CREATE DOMAIN bar AS text;
SELECT foo::bar, pg_typeof(foo::bar) FROM foo;
 foo │ pg_typeof 
─────┼───────────
 (1) │ bar
 (2) │ bar
 (3) │ bar

Custom casts to/from domains aren't supported:

A cast to or from a domain type currently has no effect. Casting to or from a domain uses the casts associated with its underlying type.

https://www.postgresql.org/docs/15/sql-createcast.html

@ilmari points out that a cast to text works:

CREATE FUNCTION foo_to_bar(t foo)
RETURNS bar
AS $$
  SELECT FORMAT('%s AS BAR', t.x)::bar
$$
LANGUAGE SQL
IMMUTABLE;

CREATE CAST (foo as text) WITH FUNCTION foo_to_bar;

SELECT foo::bar, pg_typeof(foo::bar) FROM foo;
   foo    │ pg_typeof 
──────────┼───────────
 1 AS BAR │ bar
 2 AS BAR │ bar
 3 AS BAR │ bar

SELECT foo::text, pg_typeof(foo::bar) FROM foo;
   foo    │ pg_typeof 
──────────┼───────────
 1 AS BAR │ bar
 2 AS BAR │ bar
 3 AS BAR │ bar

Answer 2

Polymorphism as a Workaround

No you can't use a cast on a domain. But, if you're trying to skirt the complexity of having different function names and different arguments you can easily do that with the polymorphism support in PostgreSQL aside from the cast system.

CREATE FUNCTION to_bucket_path(t table1) AS $$ ;defn; $$ LANGUAGE SQL;
CREATE FUNCTION to_bucket_path(t table2) AS $$ ;defn; $$ LANGUAGE SQL;
CREATE FUNCTION to_bucket_path(t table2) AS $$ ;defn; $$ LANGUAGE SQL;

This will allow the syntax like this,

SELECT to_bucket_path(foo) FROM foo;
SELECT to_bucket_path(bar) FROM bar;
SELECT to_bucket_path(baz) FROM baz;

This would work, and it's close to the idea of:

SELECT foo::bucketpath FROM foo;
SELECT bar::bucketpath FROM bar;
SELECT baz::bucketpath FROM baz;