> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> update t set t.v = p.v where t.k = p.k; >> >> At run-time, p() terminates with an obscurely worded error: >> >> 42703: column "t" of relation "t" does not exist. > > "set t.v" is simply invalid SQL and the error has nothing with this > discussion. > > https://www.postgresql.org/docs/current/sql-update.html
Well, yes, David. Please forgive me. I elided too much. This syntax: update t set t.v = 'mouse' where t.k = 1; feels like it ought to be legal. And indeed it is in Oracle Database. And so, therefore, is it legal too in embedded form in a PL/SQL procedure that uses formals or local variables in place of the manifest constants. I meant only to say “This expresses my aim. How can I spell it so that I can name the formals as I want?” I’m afraid that I was too short of time, earlier today, to take it further myself. I thought that it was the example that I’d remembered. But it simply isn’t. This works perfectly well: drop procedure if exists u1.p(int, text) cascade; create procedure u1.p(k in t.k%type, v in t.v%type) security definer language plpgsql as $body$ begin update t set v = p.v where t.k = p.k; end; $body$; It feels strange to me not to me able to qualify the name of the to-be-updated column. But I do see that this has no practical consequence. The syntax disambiguates the meaning here in a way that it cannot in a WHERE predicate. So, unless I later find a better example, I’ll assume that I can always dot qualify the name of a schema variable in PL/pgSQL code. In other words, my concern here simply falls away—which is a good thing.
