0

I want something like this:

SELECT 1, (CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END).*;

...hoping to get 1 | 2 | 3. But in PostgreSQL 11 this query throws:

ERROR: record type has not been registered

and without * it obviously returns 1 | (2,3).

Is this possible?

1
  • 1
    A CASE expression can only return one single value. You need two expressions when you want two columns
    – user1822
    Oct 12, 2019 at 6:43

1 Answer 1

2

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types.

You could define your own tuple type and use that instead of a record:

CREATE TYPE tuple AS (a int, b int);
SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*;
SELECT 1, (CASE WHEN TRUE THEN (2, 3)::tuple ELSE (4, 5)::tuple END).*;

If you absolutely need to have an expression that returns records, you can cast those to tuples afterwards only by going through a text representation:

SELECT 1, ((CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END)::text::TUPLE).*;

(online demo)

2
  • Why Postgres can infer column types for SELECT 1, s.* FROM (SELECT 2, 3) AS s ; but needs explicit type in my case? Oct 14, 2019 at 15:10
  • @KonstantinPelepelin My guess would be that a SELECT always infers some concrete row type for the result, but I don't really know either.
    – Bergi
    Oct 14, 2019 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.