1

The following is the definition of a working function I have in a postgres db:

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;

But when I try the following in BigQuery:

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;
""";

I get the following error:

Routine name "my_function" missing dataset while no default dataset is set in the request.

What is the command/syntax I am missing? I don't see a lot of documentation with concrete sql on how to correct for this.

Update: So far I have the following BigQuery I think is on the right track but it still gives errors for floating point literal 0.00 and other incompatible syntaxes

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;
Mark McGown
  • 975
  • 1
  • 10
  • 26
  • 1) just use `dataset.function_name` or `project.dataset.function_name` notion 2) if you want to use BQ scripting - you should rather use procedure - see [CREATE PROCEDURE statement](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_procedure) – Mikhail Berlyant Oct 28 '21 at 19:26
  • Thanks I'll try this right now. But can you clairify the differences between what I'm trying as a function and how it needs to be a procedure in detail? Will this simply not work as a function, is it more efficient as a proceure, etc. – Mark McGown Oct 28 '21 at 19:38
  • scripting is not supported in BQ functions. so if your logic involves scripting - proc is your option. make sense? :o) – Mikhail Berlyant Oct 28 '21 at 19:40
  • yes, it does! do you have any input on how my syntax needs to change to be the comparable bigquery version of this postgres though? – Mark McGown Oct 28 '21 at 19:53
  • your question was specifically about `Routine name missing dataset`. I recommend you to try port your existing code into BQ proc and if you still have any issue - post new/separate question and I/we will be more than happy to help! that is what we do here on SO :o) – Mikhail Berlyant Oct 28 '21 at 19:56
  • Sure thing, here it is https://stackoverflow.com/questions/69760748/proper-procedure-syntax-for-postgres-function-as-procedure-in-bigquery. If you want to post your first comment as an answer I'll mark that as the best solution and close this out. – Mark McGown Oct 28 '21 at 20:48

2 Answers2

1
  • just use dataset.function_name or project.dataset.function_name notion
  • if you want to use BQ scripting - you should rather use procedure - see CREATE PROCEDURE statement. Scripting is not supported in BQ functions. so if your logic involves scripting - proc is your option to go with
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
-1

A sample for Parameterized User Defined Function is given below:


CREATE OR REPLACE FUNCTION `Project_Name.DataSet_Name.User_Defined_Function_Name`(Field Data_Type) AS 
(
SELECT  COALESCE(Feild, 0) AS Filed2
);
Mohammad
  • 605
  • 3
  • 9