0

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)?

2 Answers 2

3

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
1
  • Here you have your first 10 points :^) Jan 24, 2023 at 18:57
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.