46317

Postgres - Return function in Case When Statement

Question:

Hey I want to create a random number. When this random number already exist i want to call the randomnumber function again and again. In that case i need to return the randomnumber function inside in CASE WHEN statement. It does not work. Still get error that number already exist. I want to create a random number for unique column:

CREATE OR REPLACE FUNCTION getrandomnumber(integer, integer) RETURNS integer AS $BODY$ DECLARE start_int ALIAS FOR $1; end_int ALIAS FOR $2; name int; BEGIN name = trunc(random() * (end_int-start_int) + start_int); CASE WHEN (select count(alias) from drivers where alias = name limit 1) = 0 THEN RETURN name; ELSE RETURN getrandomnumber(start_int, end_int); END CASE; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100; ALTER FUNCTION getrandomnumber(integer, integer) OWNER TO postgres;

Answer1:

Your function works correctly for me when run without concurrency, i.e. by one user at a time.

With:

CREATE TABLE drivers (alias integer); INSERT INTO drivers(alias) VALUES (1),(2); CREATE OR REPLACE FUNCTION ...;

then

INSERT INTO drivers(alias) VALUES (getrandomnumber(1, 5));

works twice, then fails with infinite recursion.

<hr />

Your function will not work correctly if it is called at the same time from multiple sessions. You must LOCK TABLE drivers IN EXCLUSIVE MODE or be prepared to handle unique violation errors.

I think what you really want is something more like a "random sequence" that doesn't repeat, e.g. <a href="https://wiki.postgresql.org/wiki/Pseudo_encrypt" rel="nofollow">the pseudo-encrypt function</a>.

<hr />

BTW, while:

(select count(alias) from drivers where alias = name limit 1) = 0

will work, you should probably try:

exists (select 1 from drivers where alias = name limit 1)

as it's generally faster.

Recommend

  • Using toThrowError in Jasmine
  • Input data type check in loop not working as intended (C++)
  • Making four separate variables
  • Specify source IP address for TCP socket when using Linux network device aliases
  • How to alias an executable using Powershell permanently?
  • Extend Name Box
  • mapping between two ontologies
  • Need code translation from VB to C#
  • Best practice to eliminate magic numbers within a member function
  • Eliminate partial duplicate rows from result set
  • Z3: Convert between FP and BitVector?
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Very simple C++ DLL that can be called from .net
  • Play WS (2.2.1): post/put large request
  • Counter field in MS Access, how to generate?
  • Incrementing object id automatically JS constructor (static method and variable)
  • Eraser for UIBezierPath
  • Convert array of 8 bytes to signed long in C++
  • How to limit post in wp_query
  • Upload files with Ajax and Jquery
  • Build own AppleScript numerical error handling
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • AngularJs get employee from factory
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Java static initializers and reflection
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • Change div Background jquery
  • Bitwise OR returns boolean when one of operands is nil
  • Is there any way to bind data to data.frame by some index?
  • Django query for large number of relationships
  • Sorting a 2D array using the second column C++
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Reading document lines to the user (python)
  • How to Embed XSL into XML
  • How can I use `wmic` in a Windows PE script?
  • java string with new operator and a literal
  • How to push additional view controllers onto NavigationController but keep the TabBar?