pseudo_encrypt() function in plpgsql that takes bigint

by

4294967295 must be used as the bitmask to select 32 bits (instead of 4294967296).
That’s the reason why currently you get the same value for different inputs.

I’d also suggest using bigint for the types of l2 and r2, they shouldn’t really differ from r1 and l1

And, for better randomness, use a much higher multiplier in the PRNG function to get intermediate block that really occupy 32 bits, like 32767*32767 instead of 32767.

The complete modified version:

CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE bigint) returns bigint AS $$
DECLARE
l1 bigint;
l2 bigint;
r1 bigint;
r2 bigint;
i int:=0;
BEGIN
    l1:= (VALUE >> 32) & 4294967295::bigint;
    r1:= VALUE & 4294967295;
    WHILE i < 3 LOOP
        l2 := r1;
        r2 := l1 # ((((1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767*32767)::int;
        l1 := l2;
        r1 := r2;
        i := i + 1;
    END LOOP;
RETURN ((l1::bigint << 32) + r1);
END;
$$ LANGUAGE plpgsql strict immutable;

First results:

select x,pseudo_encrypt(x::bigint) from generate_series (1, 10) as x;
 x  |   pseudo_encrypt    
----+---------------------
  1 | 3898573529235304961
  2 | 2034171750778085465
  3 |  169769968641019729
  4 | 2925594765163772086
  5 | 1061193016228543981
  6 | 3808195743949274374
  7 | 1943793931158625313
  8 |   88214277952430814
  9 | 2835217030863818694
 10 |  970815170807835400
(10 rows)

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. Is SELECT or INSERT in a function prone to race conditions?
  3. Difference between language sql and language plpgsql in PostgreSQL functions
  4. How to return result of a SELECT inside a function in PostgreSQL?
  5. Pass multiple values in single parameter
  6. GROUP BY and aggregate sequential numeric values
  7. PL/pgSQL checking if a row exists
  8. PostgreSQL parameterized Order By / Limit in table function
  9. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  10. PostgreSQL IF statement
  11. Call a set-returning function with an array argument multiple times
  12. Record returned from function has columns concatenated
  13. Postgres Dynamic Query Function
  14. Return setof record (virtual table) from function
  15. Window Functions or Common Table Expressions: count previous rows within range
  16. DROP FUNCTION without knowing the number/type of parameters?
  17. Postgres function returning table not returning data in columns
  18. Return multiple columns and rows from a function PostgreSQL instead of record
  19. COPY with dynamic file name
  20. How to perform a select query in a DO block?
  21. Function with SQL query has no destination for result data
  22. Postgres trigger after insert accessing NEW
  23. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  24. How to concatenate columns in a Postgres SELECT?
  25. PostgreSQL multi INSERT…RETURNING with multiple columns
  26. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  27. Oracle equivalent of Postgres’ DISTINCT ON?
  28. postgresql generate sequence with no gap
  29. Functions with variable number of input parameters
  30. How to use SQL LIKE condition with multiple values in PostgreSQL?

Leave a Comment