[bug]: `get_latest_partition_timestamp`: has a undeclared variable

[shivam221098]

Check the bug has not already been reported

• I could not find the bug in existing issues

Edit function_name and the short error description in title above

• I wrote the correct function name and a short error description in the title above

What happened and what did you expect?

No matter what table I pass into this procedure, it shows me this error.

As you see this bigfunction_result variable is used without its declaration.

[GaetanSoulas]

Hi, I have just encountered this issue too.

To fix it, I added an output parameter in the definition of the function :

CREATE OR REPLACE PROCEDURE get_latest_partition_timestamp (fully_qualified_table STRING, OUT bigfunction_result timestamp)

Now, I would like to add it in the source code, any idea ?

[shivam221098]

Thanks, But unfortunately BigQuery stored procedures doesn't supports INOUT variables like Postgres. It only supports parameters of IN type, which means you can pass values into the procedure but cannot return values directly through the parameters.

[shivam221098]

I fixed this and updated a few more things to handle in case of an error.

When the correct table name is passed as an argument

Results:

When table name is not fully qualified

[GaetanSoulas]

Thanks, But unfortunately BigQuery stored procedures doesn't supports INOUT variables like Postgres. It only supports parameters of IN type, which means you can pass values into the procedure but cannot return values directly through the parameters.

Hi @shivam221098, I have found the feature here, perhaps it is new :

https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#call

[shivam221098]

That's good to know.
From a BigQuery user perspective, if we implement the above logic then it will require a user to declare a variable then call the procedure then select the declared variable. It will be a three-step process

However, I still don't agree with the current architecture of the procedure because it is still a two-step process. I would say we can do a select bigfunction_result as lastest_partition_timestamp within the procedure so that it will be a one-step process to get the result.

What are your opinions @unytics?