Postgres JavaScript missing variables (But it is #$%^ there!)

It’s OK

I only cried and contemplated quitting working in IT and becoming a Nomad for a couple of hours.

But I got there in the end; but the following error message will probably plague my nightmares for a couple of weeks:

ERROR:  ReferenceError: inNumber1 is not defined
CONTEXT:  fn_js_number_adder() LINE 2: 	let total = inNumber1 + inNumber2 

SQL state: XX000

JavaScript: When in Rome – Do what the Romans do

My job today was to write a JavaScript function in Postgres to convert byte hex values to EBCDIC 037. The aim is to decommission some duplicate pipelines coming from our DB2 database by Qlik Replicate that deliver ASCII converted fields as well as the EBCDIC version.

I haven’t worked in JavaScript since my Uni days and well entrenched in the Python world for my day to day job. Over the past years I have converted using naming conventions in code from camelCase to under_score to match Python’s standard.

So going back to JavaScript – I knew that camelCase is the expected format. Since I didn’t know where my code was going to end up; I wanted it to look professional as it is a reflection on me.

So I wrote a JavaScript function paraphrased as:

CREATE OR REPLACE FUNCTION fn_js_number_adder(inNumber1 numeric, inNumber2 numeric)
RETURNS numeric
as
$$
	let total = inNumber1 + inNumber2

	return total
$$
LANGUAGE plv8;

Looks good – compiles with no errors.

But when I went to test it; I get the following error:

The error drove me crazy! It’s THERE! The variable is THERE!

The original function was a lot more extensive than above so I cut as much out of it as possible in case something else was causing the variable not to be recognised.

Still no luck.

I went to the functions section in pgAdmin as I wanted to compare it against an existing function I created to see what the difference was.

Interesting…

The function’s parameters have changed from inNumber1 and inNumber2 to innumber1 and innumber2.

Scripting out the function I got:

CREATE OR REPLACE FUNCTION public.fn_js_number_adder(
	innumber1 numeric,
	innumber2 numeric)
    RETURNS numeric
    LANGUAGE 'plv8'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
	let total = inNumber1 + inNumber2

	return total
$BODY$;

So; either postgres or pgAdmin changed the case of the parameters from camelCase to lower case. This caused the variable not to be found later in the code.

The fix – Back to under_scores we go

My fix for this instance (whether standard or not) is to go back to under_scores:

CREATE OR REPLACE FUNCTION fn_js_number_adder(in_number1 numeric, in_number2 numeric)
RETURNS numeric
as
$$
	let total = in_number1 + in_number2

	return total
$$
LANGUAGE plv8;

This works and I could run the function

With the naming conventions; I suppose using under_score isn’t too much of a sin since it is a standard on databases. If you want to stay true to camelCase; the parameters can just be in under_score and the rest of the variables be in camelCase.

At lest it is working…now onto EBCDIC conversion.

Tags: functionsjavascriptpostgresReferenceError