Postgresql howto dynamically change parameter value-CodePudding

Is it possible to dynamically set param of numeric() ? eg:

CREATE OR REPLACE somefunction() RETURNS numeric AS  
DECLARE    
f numeric;
x integer;  
BEGIN
    x := 2;
    SELECT INTO f CAST(something AS numeric(12, x));
    RETURN f;
END;

So, I don't need to use CASE inside my plpgsql function if possible :) Tried everything, but it does not work, cast expects constant. Thanks:)

CodePudding user response：

Using Dynamic query:

CREATE OR REPLACE FUNCTION public.somefunction(something numeric)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
DECLARE
f numeric;
x integer;
BEGIN
    x := 2;
    EXECUTE 'SELECT  CAST($1 AS numeric(12, ' || x ||'))' INTO f USING something;
    RETURN f;
END;
$function$
;

select somefunction(126.787);
 somefunction 
--------------
       126.79

Alternate where you pass in the scale:

CREATE OR REPLACE FUNCTION public.somefunction(something numeric, scale integer)
 RETURNS numeric
 LANGUAGE plpgsql
AS $function$
DECLARE    
f numeric;
BEGIN
    EXECUTE 'SELECT  CAST($1 AS numeric(12, ' || scale ||'))' INTO f USING something;
    RETURN f;
END;
$function$
;

select somefunction(126.787,2);
 somefunction 
--------------
       126.79
(1 row)

select somefunction(126.787,1);
 somefunction 
--------------
        126.8