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
Last activity: Jan 24, 2023, 07:42 PM