Postgres struct literal syntax-CodePudding

Is it possible to create an inline struct in Postgres, or does it first need to be define with a custom type? For example, I am familiar doing:

create type PERSON as (first varchar, last varchar);
select PERSON '("tom", "jones")'

But is there a way to just do something like:

SELECT {first: 'tom', last: 'jones'} as PERSON

Or something like that?

Reference: https://www.postgresql.org/docs/current/rowtypes.html

CodePudding user response：

Postgres has many features that allow you to adjust it to specific expectations. It would be easier to answer the question if it contained information about what type of problem the OP wants to solve. Of course, the expected notation is not possible in Posgres syntax, but we can try to find something like that.

If you just want a kind of constructor where you name each element of a composite type, this function can help:

create or replace function to_person(first varchar, last varchar)
returns person language sql immutable as $$
    select(first, last)
$$;

select to_person(first => 'tom', last => 'jones');

Alternatively, if you want to create a value of a composite type with the json object:

create or replace function to_person(arg jsonb)
returns person language sql immutable as $$
    select(arg->>'first', arg->>'last')
$$;

select to_person('{"first": "tom", "last": "jones"}');

Test it in db<>fiddle.