CODEWITHSUNDEEP
postgresqltypescasting
Johnson
Johnson

Reputation: 295

PostgreSQL nested UDTs ans CAST syntax

I have few UD types:

CREATE TYPE SOME_TYPE AS (
    aaa INT,
    bbb TEXT
);
CREATE TYPE SOME_ANOTHER_TYPE AS (
    ccc int,
    ddd SOME_TYPE
);
CREATE TYPE SOME_ANOTHER_DAMNIT_TYPE AS (
    eee int,
    fff SOME_ANOTHER_TYPE
);

Okay, i can make first from SQL:

select '(123,blablabla)'::SOME_TYPE;

And i can make second:

select '(456, "(123,blablabla)")'::SOME_ANOTHER_TYPE;

But if i try to make third...

select '(789,"(456, "(123,blablabla)")")'::SOME_ANOTHER_DAMNIT_TYPE;

... pl throw error:

[22P02] ERROR: malformed record literal: "(456, "(123 Подробности:

Unexpected end of input.

Question: how to make third type without plpgsql? I need it for calling from jdbc, to transfer object. I know, i can 'flatten' this object, but i want transfer it 'as is'.

Upvotes: 0

Views: 132

Answers (1)

redneb
redneb

Reputation: 23870

The problem is that you have nested the quotes without escaping the inner quotes. You could that like so:

select '(789,"(456, ""(123,blablabla)"")")'::SOME_ANOTHER_DAMNIT_TYPE;

i.e. by doubling the ".

For your case, I would suggest to avoid representing your record values as text only to cast them as your type later on. You can specify the record values directly, without going through text like so:

select (789,(456, (123,'blablabla')))::SOME_ANOTHER_DAMNIT_TYPE;

Upvotes: 1

Related Questions