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 without table qualification like before, because plpgsql would raise an exception (not strictly necessary, but probably useful in most cases):ts
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?