How to use variable as field type in plpgsql?

2
votes

I'm trying to use lookup_type value from classificator table to cast value into appropriate type.

                                 Table "public.classificator"
      Column      |  Type  | Collation | Nullable |                  Default                  
------------------+--------+-----------+----------+-------------------------------------------
 classificator_id | bigint |           | not null | nextval('classificator_id_seq'::regclass)
 classificator    | text   |           | not null | 
 lookup           | text   |           | not null | 
 description      | text   |           |          | 
 lookup_value     | text   |           |          | 
 lookup_type      | text   |           |          |

I would use for example values ('SYSTEM_SETTINGS', 'daylight_saving_enabled', 'Use daylight saving for system or not', 'true', 'boolean').

I'm having troubles using variable as type.

psql test:

select cast('1' as integer); --> OK

select cast('1' as 'integer');
ERROR:  syntax error at or near "'integer'"

As I'm trying to do this in plpgsql, I have 2 options, how to solve this:

1) 

EXECUTE 'SELECT CAST($1 AS ' || 'boolean' || ')'
  INTO value                                                              
 USING 'true';

2) Create a function with a lot of ifs, that returns casted values e.g. IF type = 'boolean' THEN return 'value'::boolean etc.

Is there a more elegant solution for this problem?

1
sqlpostgresqlplpgsql
On a second thought, function is not doable, as it has to have specific return type :) - user1190411
To others answerers who come: Don't go down the route of "Well maybe you can have a function that returns record..." - I spent a while mucking around with that before remembering that when using the output of such functions, you have to specify the cast anyway. - Scoots

1 Answers

1
votes

Your solution 1) is mostly the right one.

I don't think there is an elegant solution to that.

To make it secure, write:

EXECUTE format('SELECT CAST($1 AS %I)', 'bool')
   INTO value                                                              
   USING 'true';