Page 1 of 1

Masking - UDF

Posted: Tue Mar 17, 2015 5:38 pm
by vigneshn
Hi,

I am planning to write a function, to mask the data in the table. The masking should be reversible. So I tried a logic in C program. Please find attached the C program.

This program, shuffles the position of data

The Sample Output of the C program shuffle.c

$ ./1.out vignesh
The argument supplied is vignesh
The value of argv[1] is vignesh
The value of arr is vignesh
The value of arr is ihvnsge

By just changing a for loop in the same code, I can bring back the shuffled word to the correct word

Please find the sample output of the C reshuffle.c This program is also attached.

infinity@infinity-PC:~$ ./reshuffle.out ihvnsge
The argument supplied is ihvnsge
The value of argv[1] is ihvnsge
The value of arr is ihvnsge
The value of arr is vignesh

I want to know , whether these two programs can be written as user defined function. If so , someone can guide me how to do that.

Please help needed.

Re: Masking - UDF

Posted: Thu Mar 19, 2015 3:14 pm
by JimKnicely
Maybe you could make use of the built-in TRANSLATE function instead of writing your own?

Simple example:

Code: Select all

dbadmin=> CREATE OR REPLACE FUNCTION shuffle (x VARCHAR)
dbadmin-> RETURN VARCHAR AS
dbadmin-> BEGIN
dbadmin->   RETURN translate(translate(x, 'abcdefghijklmnopqrstuvwxyz', 'zyxwvutsrqponmlkjihgfedcba'),
dbadmin(>            'zyxwvutsrqponmlkjihgfedcba', 'mlkjihgfedcbazyxwvutsrqpon');
dbadmin-> END;
RETURN VARCHAR AS
BEGIN
CREATE FUNCTION
dbadmin=>
dbadmin=> CREATE OR REPLACE FUNCTION unshuffle (x VARCHAR)
dbadmin-> RETURN VARCHAR AS
dbadmin-> BEGIN
dbadmin->   RETURN translate(translate(x, 'mlkjihgfedcbazyxwvutsrqpon', 'zyxwvutsrqponmlkjihgfedcba'),
dbadmin(>            'zyxwvutsrqponmlkjihgfedcba', 'abcdefghijklmnopqrstuvwxyz');
dbadmin->
dbadmin-> END;
CREATE FUNCTION
dbadmin=> SELECT c, shuffle(c), unshuffle(shuffle(c)) FROM t;
        c        |     shuffle     |    unshuffle
-----------------+-----------------+-----------------
 jim             | dea             | jim
 sarah           | umvmf           | sarah
 vignesh         | regziuf         | vignesh
 zookeeper willy | nyyciixiv qebbo | zookeeper willy
 joshua          | dyufsm          | joshua
 jingxuan        | dezgpsmz        | jingxuan
(6 rows)

Re: Masking - UDF

Posted: Thu Mar 19, 2015 8:05 pm
by scutter
You can also create a UDF in Java or C++ — they are straightforward enough to create.

—Sharon