Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)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.