Naming conflict between function parameter and result of JOIN with USING clause

Question

Given this setup in current Postgres 9.4 (from this related question):

CREATE TABLE foo (ts, foo) AS 
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle here (also from the previous question).

I wrote a SELECT with a FULL JOIN to achieve the objective of the referenced question. Simplified:

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

As per specifications, the correct way to address the column ts is without table qualification. Either of the input values (f.ts or b.ts) can be NULL. The USING clause creates a bit of an odd case: introducing an "input" column that's not actually present in the input. So far so elegant.

I put this in a plpgsql function. For convenience (or requirements) I want the same column names for the result of the table function. So we have to avoid naming conflicts between identical column names and function parameters. Should best be avoided by picking different names, but here we are:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

Bold emphasis to highlight the problem. I can't use ts without table qualification like before, because plpgsql would raise an exception (not strictly necessary, but probably useful in most cases):

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

I know I can use different names or a subquery or use another function. But I wonder if there's a way to reference the column. I can't use table-qualification. One would think there should be a way.
Is there?

Answer 1

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or at the start of the function definition, declaring how you want such conflicts to be resolved.
The 3 possible settings are error (the default), use_variable and use_column:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;

Answer 2

Postgres 14 adds SQL syntax addressing this problem. Now you can append an AS clause to declare a new table alias for the columns merged in the USING list:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT merged.ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts) AS merged
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;

The manual:

USING ( join_column [, ...] ) [ AS join_using_alias ]

[...]

If a join_using_alias name is specified, it provides a table alias for the join columns. Only the join columns listed in the USING clause are addressable by this name. Unlike a regular alias, this does not hide the names of the joined tables from the rest of the query. Also unlike a regular alias, you cannot write a column alias list — the output names of the join columns are the same as they appear in the USING list.