Обсуждение: Convert text to user defined datatype
Maybe needless,
xxx=> create type t2 as (t2 varchar(30)[]);
CREATE TYPE
xxx=> select ('("{a,b,c}")')::t2;
t2
-------------
("{a,b,c}")
(1 row)
From: aditya desai <admad123@gmail.com>
Sent: Saturday, October 23, 2021 11:40 AM
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Convert text to user defined datatype
CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi,
I have a user defined data type as below.
postgres=# \d t2;
Composite type "public.t2"
Column | Type | Collation | Nullable | Default
--------+-------------------------+-----------+----------+---------
t2 | character varying(30)[] | | |
I need to cast values to the above type. Getting error below.
postgres=# CREATE CAST (text as t2) without function;
ERROR: source and target data types are not physically compatible
If I have to create a CAST with function. Could you please suggest how to write this function?
Regards,
Aditya.
On Saturday, October 23, 2021, Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com> wrote:
Maybe needless,
xxx=> create type t2 as (t2 varchar(30)[]);
CREATE TYPE
xxx=> select ('("{a,b,c}")')::t2;
t2
-------------
("{a,b,c}")
(1 row)
From: aditya desai <admad123@gmail.com>
Sent: Saturday, October 23, 2021 11:40 AM
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Convert text to user defined datatype
CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi,
I have a user defined data type as below.
postgres=# \d t2;
Composite type "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------------------
----+-----------+----------+-- ------- t2 | character varying(30)[] | | |
I need to cast values to the above type. Getting error below.
postgres=# CREATE CAST (text as t2) without function;
ERROR: source and target data types are not physically compatible
If I have to create a CAST with function. Could you please suggest how to write this function?
Regards,
Aditya.
aditya desai <admad123@gmail.com> writes:
> Thanks Jean. However I am trying this inside procedure and getting an
> error. Will try to send screenshot.
That's frowned on around here. Can't you copy-and-paste the code
and the error message?
As Jean-Marc says, you shouldn't really need a defined CAST
object to cast to or from text; Postgres will interpret that
as a request to apply the datatype's I/O functions.
You can formalize that by creating a cast "WITH INOUT", but
you shouldn't need to, so I think your problem here is something
different from what you said. Hard to give more advice without
seeing the problem code and the specific error.
regards, tom lane
aditya desai <admad123@gmail.com> writes:
> Thanks Jean. However I am trying this inside procedure and getting an
> error. Will try to send screenshot.
That's frowned on around here. Can't you copy-and-paste the code
and the error message?
As Jean-Marc says, you shouldn't really need a defined CAST
object to cast to or from text; Postgres will interpret that
as a request to apply the datatype's I/O functions.
You can formalize that by creating a cast "WITH INOUT", but
you shouldn't need to, so I think your problem here is something
different from what you said. Hard to give more advice without
seeing the problem code and the specific error.
regards, tom lane