postgresql plpgsql compiler: why is it finding ambiguity where there is none

Question

Why this runtime error?

ERROR: column reference "arrive" is ambiguous LINE 6: case when ( (cast('05:00' as time) >= arrive)
DETAIL: It could refer to either a PL/pgSQL variable or a table column.

The thing is, the case statement is in a query, and I'm selecting from a table that does have a column called "arrive". I have not declared a variable called arrive. Why can't PG simply check to see that there's no such variable declared, and conclude that the reference must be to the column?

I am attached the code below. The only ostensible conflict I see is in the definition of the outgoing table returned by this function, in which there's a column called "arrive", and the use of the column name in the final select against the temporary table TT.

CREATE or replace function GetHourlyView
(v_whichDate date)
returns TABLE
(
stagecoach,
arrive time,
depart time,
t5am int,t6am int,t7am int,t8am int,t9am int,t10am int, t11am int,
t12pm int, t1pm int, t2pm int t3pm int,t4pm int,t5pm int,t6pm int, 
t7pm int, t8pm int, t9pm int, t10pm int, t11pm int
)
as $body$
declare v_dow int := date_part('dow',v_whichDate);
begin

drop table if exists TT;
create temp table TT
(stagecoach varchar(25),
arrive time,
depart time,
t5am int,t6am int,t7am int,t8am int,t9am int,t10am int, t11am int,
t12pm int, t1pm int, t2pm int t3pm int,t4pm int,t5pm int,t6pm int, 
t7pm int, t8pm int, t9pm int, t10pm int, t11pm int
) without OIDS on commit drop;

insert into TT 
select * from
GetDailySchedule( v_whichDate);

-- (arrive=depart) means 'cancelled'
delete from TT where TT.arrive=TT.depart;   

return QUERY
select 
TT.stagecoach, 
arrive, 
depart,
case when ( (cast('05:00' as time) >=  arrive) and  (cast('05:00' as time) <  depart )) then 1    else 0 end as t5am,
case when ( (cast('06:00' as time) >=  arrive) and  (cast('06:00' as time) <  depart )) then 1 else 0 end as t6am,


<snip>
.
.
.
case when ( (cast('23:00' as time) >=  arrive) and  (cast('23:00' as time) <  depart )) then 1    else 0 end as t11pm
from TT
;

drop table TT;
end
$body$
LANGUAGE 'plpgsql'

Please show your code (the full PL/PgSQL function, or at least the line giving the error and the code from `CREATE OR REPLACE FUNCTION` until the end of the `DECLARE` block and start of the `BEGIN` block) and mention your PostgreSQL version. Comment here when you've edited your question; edits don't send notifications. — Craig Ringer, Jan 26 '13 at 23:38
try to use alias on column arrive. Maybe there is some join with table that has got the column arrive as well. or you use selfjoin? but anyway the code will be helpful. — Borys, Jan 26 '13 at 23:41
@Borys: aliasing the column solves the problem, thanks, but I'm including the function since I'm not sure where postgres plpgsql sees the conflict. — Tim, Jan 27 '13 at 12:01
I'm not sure what you are trying to say. Is it that because the query inside the function is based in another function so there is no reason to report conflict? If that's your question I would say everytime you call some funcion inside another one the main function has got acces to this one inside and the conflict can appear, as well as with subqueries. — Borys, Jan 27 '13 at 12:25
@Borys: when functionA invokes functionB, there are not two separate scopes? It's as if the two functions are merged into a single scope? — Tim, Jan 27 '13 at 12:39
Yes, they all share the same transaction scope. Check it in here http://www.postgresql.org/message-id/d6d6637f0901140747t90b4d00q2b35af813d3ebc9@mail.gmail.com — Borys, Jan 27 '13 at 12:49
Thanks for clearing that up. I did not fully understand the implications of transaction scope. — Tim, Jan 27 '13 at 13:07
@Borys: if the inner function does `select TT.arrive, TT.depart from TT100 as TT` and the outer function does `select TT.arrive, TT.depart from TT200 as TT` does the table-alias "TT" conflict also? — Tim, Jan 27 '13 at 13:33
Yes, it might it's the same situation as above. Another tip to solve variable/table column issue could be using the configuration parameter plpgsql.variable_conflict by inserting one of these special commands at the start of the function text http://www.postgresql.org/docs/9.2/static/plpgsql-implementation.html — Borys, Jan 27 '13 at 13:43
There is **no** conflict if you reuse the same table alias in multiple independent SQL statements inside the function body. None at all. And parameter names are only visible inside the function, not inside other functions called from the function. The information in the comments above is *not* accurate. "Transaction scope" has nothing to do with it. You also do **not** need to use an alias for column names. Just table-qualify them. — Erwin Brandstetter, Jan 27 '13 at 19:04

Erwin Brandstetter · Answer 1 · 2013-01-27T22:59:52.713

It's rather simple really: function parameters are visible everywhere inside the function body (except for dynamic SQL). That's true for all parameters: IN, OUT, INOUT, VARIADIC and any column name used in a RETURNS TABLE clause.
You also had a couple of other minor errors.

Avoid conflicts by table-qualifying column names: tt.arrive instead of just arrive.
Missing type for stagecoach in RETURNS TABLE.
Don't single-quote plpgsql at the end. It's an identifier.

I would also advise to adjust your syntax style. You are living in opposite world. The convention is to upper case SQL key words and lower case identifiers, not the other way round. Remember that PostgreSQL automatically casts unquoted identifiers to lower case.

All this aside, your function can be largely simplified to a plain SQL query. I wrapped it into an SQL function:

CREATE OR REPLACE FUNCTION gethourlyview(v_whichdate date)
  RETURNS TABLE (
    stagecoach text, arrive time, depart time
   , t5am  int, t6am  int, t7am int, t8am  int, t9am  int, t10am int, t11am int
   , t12pm int, t1pm  int, t2pm int, t3pm  int, t4pm  int, t5pm  int, t6pm  int
   , t7pm  int, t8pm  int, t9pm int, t10pm int, t11pm int
   ) AS
$body$
   SELECT tt.stagecoach
         ,tt.arrive -- "depart" would cause conflict
         ,tt.depart
         ,CASE WHEN '05:00'::time >= tt.arrive
                AND '05:00'::time <  tt.depart THEN 1 ELSE 0 END -- AS t5am
         ,...
         ,CASE WHEN '23:00'::time >= tt.arrive
                AND '23:00'::time <  tt.depart THEN 1 ELSE 0 END -- AS t11pm
   FROM   getdailyschedule($1) tt
   WHERE  tt.arrive IS DISTINCT FROM tt.depart;
$body$ LANGUAGE sql;

No need to create a temporary table. You can do it all in one statement.

postgresql plpgsql compiler: why is it finding ambiguity where there is none

1 Answers1