Question 88977 in PostgreSQL Question posted in
The official documentation can be found here.

I have to write a procedure in postgresql, which will find the maximum value of column having alphanumeric value(text) like 'RAM-1', 'RAM-2'

I have a column name as Item_code which have the values like ‘RAM-1′,’RAM-2’, ‘RAM-2’….’RAM-12’. I have to write procedure in postgreSQL which find maximum value from this column.

CREATE OR REPLACE FUNCTION public.get_last_itemcode(
item text)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare 
result text;      
BEGIN
   Select MAX(it.item_code) into result from public.items as it where it.item_name = item;

   return result;
END;
$BODY$;

But it gives the value maximum value ‘RAM-9, instead of ‘RAM-15’.

Tags:

2

Answers


  1. 0

    The following should satisfy the stated requirements:

    CREATE OR REPLACE FUNCTION get_last_itemcode(item items.item_name%type)
  RETURNS items.item_code%type
  LANGUAGE plpgsql
  STRICT
  AS $BODY$
DECLARE
  last_itemcode items.item_code%type;
BEGIN
  SELECT items.item_code
    INTO last_itemcode
    FROM items
   WHERE items.item_name = get_last_itemcode.item
   ORDER BY regexp_replace(items.item_code, '^.*-(d+)$', '1')::integer DESC
   LIMIT 1;

  RETURN last_itemcode;
END
$BODY$;

    STRICT has been specified since the function will always return NULL if item is NULL. All of the column and variable references in the query are qualified to protect against name collisions. Type declarations reference the relevant table columns to help protect against incompatible schema changes.

    Login or Signup to reply.
  2. 0

    To get the max, you will need to descending order records by the extracted number from item_code, then take the first one using LIMIT 1:

    CREATE OR REPLACE FUNCTION public.get_last_itemcode(item text)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
declare 
result text;      
BEGIN
   Select item_code into result
   from public.items 
   where item_name = item
   order by REGEXP_REPLACE(item_code, '.*-(^0-9)*', '1')::int desc
   limit 1;

   return result;
END;
$BODY$;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search