One fun fact of Pl\pgSQL is that it’s an Extension in PostgreSQL that is created as default whenever we create any new databases.

Converting databases, primarily those like Oracle, involves translating multiple functions, procedures, or packages written in PL/SQL to PostgreSQL with PL/pgSQL.During the conversion stage, we utilize conversion tools to automate a significant portion of the initial migration. However, it’s important to note that automatically converted code might not always follow best practices for PostgreSQL development.

It’s very critical to include code review as part of migrations. Whenever the opportunity arises, we should incorporate best practices. Sharing one such learning experience with conversion helped me learn some hidden PL/pgSQL intricacies.

Let’s consider below snippet of sample PL\pgSQL code for our reference.

create table testproc(seqno integer, commit bool, comments1 text , comments2 text);
insert into testproc values(1,true, 'sample1', null);

create or replace function func1(seqno bigint, comments1 text ) 
returns setof testproc language plpgsql as 
$$ 
begin
    return query update testproc 
                 set comments2=comments1 
                 where seqno=seqno
    RETURNING *;
end;
$$;

Please take a re-look at sample procedure, below are some key highlights.

• Tables, column names, and variables used in functions often have the same names.
• Inconsistent variable naming conventions are present.

Let’s run the sample code and see what output we get as default.

=> select func1(2,'sample3'); 

ERROR:  column reference "seqno" is ambiguous
LINE 1: update testproc set comments2=comments1 where seqno=seqno
                                                      ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  update testproc set comments2=comments1 where seqno=seqno
    RETURNING *
CONTEXT:  PL/pgSQL function func1(bigint,text) line 3 at RETURN QUERY

Ambiguous references within procedural code can be a challenge. Let’s walk through some solutions we can incorporate to resolve them effectively.

Using #variable_conflict use_variable

PL/pgSQL offers multiple options for resolving variable conflicts within procedural blocks. By default, it’s set to “error,” which is why it failed in the previous execution when resolving conflicts.

=> \dconfig plpgsql.variable_conflict
 List of configuration parameters
         Parameter         | Value 
---------------------------+-------
 plpgsql.variable_conflict | error
(1 row)

We have different options like use_variable and use_column that we can enable for specific functions as below.

create or replace function func1(seqno bigint, comments1 text ) 
returns setof testproc language plpgsql as 
$$ 
#variable_conflict use_variable
begin
    return query update testproc 
                 set comments2=comments1 
                 where seqno=seqno
    RETURNING *;
end;
$$;

Lets re-run the functions with same input argument, please note seqno that is pass does not exists.

=> select func1(2,'sample3'); 
         func1         
-----------------------
 (1,t,sample1,sample3)
(1 row)

Conflicting references to ambiguous names are resolved only in the SET component of an UPDATE statement. However, the WHERE clause can still reference these variables, potentially leading to incorrect updates. To avoid this, we need to add additional aliases for ambiguous names. using Aliasing will help us to resolve it further.

Using Blockname or Function name as Aliases.

All procedural block within functions or procedure is with a block that is same as the function or procedure itself

Using block name as reference help us to distinguish functions or procedure variable but it is always advice to have proper aliasing for column references as well.

create or replace function func1(seqno bigint, comments1 text ) 
returns setof testproc language plpgsql as 
$$ 
begin
    return query update testproc 
    set comments2=func1.comments1 
   where testproc.seqno=func1.seqno
    RETURNING *;
end;
$$;

dcg=> select func1(2,'sample3');                                                                                                        func1 
-------
(0 rows)

Conclusion

Whether during conversions or new development, it’s essential to avoid variable names that could create ambiguity within functions or procedures. Using proper aliasing using the default block or table level helps to overcome these conflicts. In some cases, you can also leverage the plpgsql.variable_conflict configuration at the function level.

If you really enjoyed the blog and are exploring courses to master PL/pgSQL development on PostgreSQL, please check out my Live courses.