1

I have the following function in postgres:

create function my_function(price numeric, qty numeric, min_charge numeric, other_fee numeric) returns numeric
    language plpgsql
as
$$
DECLARE
    _charge numeric;
BEGIN
    IF qty = 0 THEN
        RETURN 0.00::numeric(19, 2);
    END IF;
    _charge := GREATEST(price * qty, min_charge)::numeric(19, 2);
    RETURN (_charge + COALESCE(other_fee, 0.00))::numeric(19, 2);
END;
$$;

alter function my_function(numeric, numeric, numeric, numeric) owner to my_location;

I'd like to convert this into BigQuery for the same functionality and so far I've tried:

create procedure my_schema.my_function(price numeric, qty numeric, min_charge numeric, other_fee numeric)
BEGIN
    IF qty = 0 THEN
        RETURN 0.00::numeric(19, 2);
    END IF;
    _charge := GREATEST(price * qty, min_charge)::numeric(19, 2);
    RETURN (_charge + COALESCE(other_fee, 0.00))::numeric(19, 2);
END;

But I'm getting the following error:

Syntax error: Expected ";" but got floating point literal "0.00" at [4:16]

What is the proper command to produce the same postgres-style-function as a procedure successfully in BigQuery?

Mark McGown
  • 975
  • 1
  • 10
  • 26

2 Answers2

0

I was able to create the procedure in BigQuery by replacing the return data type to FLOAT64. Try this script:

CREATE PROCEDURE mydataset.my_function(price numeric, qty numeric, min_charge numeric, other_fee numeric)
  BEGIN
    IF qty = 0 THEN 
        SELECT 0.00 AS FLOAT64;
    END IF;
     SELECT (GREATEST(price * qty, min_charge)  + COALESCE(other_fee, 0.00)) AS FLOAT64;
  END;

enter image description here

Mabel A.
  • 1,775
  • 4
  • 14
0

I'd like to convert this into BigQuery for the same functionality

Consider below example

create temp function my_function(price numeric, qty numeric, min_charge numeric, other_fee numeric) 
returns numeric as (
  case when qty = 0 then 0
    else greatest(price * qty, min_charge) + coalesce(other_fee, 0)
  end
);
select my_function(1, 1, 1, 1)

if you want permanent function - just remove temp in create temp function

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230