Sample Header Ad - 728x90

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

0 votes
2 answers
374 views
Let's say I have a simple table foo, and [domain](https://www.postgresql.org/docs/current/domains.html) 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)?
Asked by Evan Carroll (65502 rep)
Jan 24, 2023, 05:56 PM
Last activity: Jan 24, 2023, 07:42 PM