Greenplum Database (GPDB) 6.x ERROR: parameter name "<Param_Name>" used more than once

Products

• VMware Tanzu Greenplum

Issue/Introduction

When a function uses the duplicate parameter names, it throws the following error message:

parameter name "<Param_Name>" used more than once

Environment

Product Version: 6.11

Resolution

If function definition contains duplicate names, it will throw the following error:

CREATE or replace FUNCTION dup_para(in int, out f1 int, out f1 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL;
ERROR:  parameter name "f1" used more than once

In Greenplum Database (GPDB) 5.x, the use of parameter names is not enforced inside a function. This means the parameter cannot be used inside the function body and you will not see this error.

However, in GPDB 6.x version, we allow a function to refer to the parameter names, which enforces natural uniqueness. 

Workaround

For this issue, the workaround is to change the parameter names to be unique:

CREATE or replace FUNCTION dup_para(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL;
CREATE FUNCTION