I would like to building an insert query on the fly. Here I am collecting all column names. Some columns would only need to be scrambled, and some would need to be decrypted, scrambled and encrypted.
My idea was to check if the column name is in the provided array, and if it is, check to see if its value start with 'pi2'. If so, the apply the necessary transformation decrypt, translate, encrypt. else just translate the value. Here is my query:
select
case when column_name = ANY(ARRAY['a', 'pi2_b', 'pi2_c', 'd', 'e'])
then (
if (substring(column_name from 0 for 4) = 'pi2')
then 'my_function.my_encrypt(translate(my_function.my_decrypt('||column_name ||'), '''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||'''))';
else 'translate('||column_name||','''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||''')';
end if; )
else column_name end
from information_schema.columns
where table_schema = 'my_temp'
and table_name = 'table_a'
order by ordinal_position ASC
Running this query gives me a syntax error:
at or near 'then' SQL state: 42601 Character: 177.
My question is why can't I use the IF THEN ELSE inside my CASE statement.
I have this workaround:
select
case when column_name = ANY(ARRAY['a', 'pi2_b', 'pi2_c', 'd', 'e']) AND substring(column_name from 0 for 4) = 'pi2'
then 'my_function.my_encrypt(translate(my_function.my_decrypt('||column_name ||'), '''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||'''))';
when column_name = ANY(ARRAY['a', 'pi2_b', 'pi2_c', 'd', 'e'])
then 'translate('||column_name||','''|| var_scramble_key1 ||''','''|| var_scramble_key2 ||''')';
else column_name end
from information_schema.columns
where table_schema = 'my_temp'
and table_name = 'table_a'
order by ordinal_position ASC
Aucun commentaire:
Enregistrer un commentaire