CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user1190411
user1190411

Reputation: 341

How to use variable as field type in plpgsql?

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?

Upvotes: 2

Views: 247

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246513

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';

Upvotes: 1

Related Questions