3

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?

forkfork
  • 415
  • 6
  • 22
  • I currently don't know if this is possible. I'm curious though how do you define `WHERE id = 2` I mean, if you know which data type to look means you already know the data type right ? – Jorge Campos Apr 10 '19 at 18:23
  • And, this may help you out: https://stackoverflow.com/questions/3827828/how-to-check-type-of-value-in-postgres/43781719 – Jorge Campos Apr 10 '19 at 18:25
  • This is not really possible -- unless you use dynamic SQL. The type of a column is fixed in the `SELECT`; it is not variable. – Gordon Linoff Apr 10 '19 at 18:40
  • No, it is not possible. The syntax parser of PostgreSQL denies any dynamic element here. Even if it would be possible, you'd have to make sure that conversion between the source and target type is actually possible. Is there a concrete reason you think you'd need the database to do that instead of the application? – Ancoron Apr 10 '19 at 19:21

1 Answers1

3

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228