PostgreSQL - How to cast dynamically?

Question

I have a column that has the type of the dataset in text.

So I want to do something like this:

SELECT CAST ('100' AS %INTEGER%);
SELECT CAST (100 AS %TEXT%);

SELECT CAST ('100' AS (SELECT type FROM dataset_types WHERE id = 2));

Is that possible with PostgreSQL?

Answer 1

SQL is strongly typed and static. Postgres demands to know the number of columns and their data type a the time of the call. So you need dynamic SQL in one of the procedural language extensions for this. And then you still face the obstacle that functions (necessarily) have a fixed return type. Related:

• Dynamically define returning row types based on a passed given table in plpgsql?
• Function to return dynamic set of columns for given table

Or you go with a two-step flow. First concatenate the query string (with another SELECT query). Then execute the generated query string. Two round trips to the server.

1. SELECT '100::' || type FROM dataset_types WHERE id = 2; -- record resulting string

2. Execute the result. (And make sure you didn't open any vectors for SQL injection!)

About the short cast syntax:

• Postgres data type cast