). It implies the question:
I couldn't find more than what I quoted. Have I missed something?
Anyway, I tried some tests. Here's an example that aims to make a reasonable compromise between brevity and its capacity to illustrate. First, I create a domain and then leave it unchanged:
create domain tt as text[];
Then I do this:
create or replace function f()
returns text
language plpgsql
as $body$
declare
n int;
arr tt := array['dog', 'cat'];
begin
n := (select count(*) from (select unnest(art)) as a);
return n::text;
end;
$body$;
\sf+ f()
select f();
The "create or replace" completes without error and the "select" runs to produce the result, 2, that I expect.
If I simulate a typo by changing "n" on the LHS of the assignments to "m", then I get this error at "create or replace" time:
"m" is not a known variable
Moreover, "\sf+" shows that the former definition has remained intact—as I've come to expect.
If I fix the "n" typo and simulate a second typo by changing "tt" in the declaration of "arr" to "tz", then I get this error at "create or replace" time:
type "tz" does not exist
If I fix the "tz" typo and simulate a third typo by changing "arr" in the scalar subquery expression to "art", then "create or replace" completes without error and "\sf+" confirms that the new source is in place. Then, at "select" time, I get this error:
column "art" does not exist
So far, I'm tempted to think that "parse" covers everything about "regular" (i.e. not embedded SQL) PL/pgSQL statements, including syntactic analysis *and* the resolution of identifiers—both within the scope of the to-be-created subprogram and within schema scopes.
But, as it seems, embedded SQL statements receive only syntactic analysis—leaving the resolution of identifiers (even when this can be done in the scope of the to-be-created subprogram) to runtime. (I tried changing "from" to "frim" and that caused a syntax error.)
Then I dropped "f()" and extended the test, thus:
create or replace function f()
returns table(z text)
language plpgsql
as $body$
declare
v_sqlstate text not null := '';
v_message text not null := '';
n int;
arr tt := array['dog', 'cat'];
begin
z := (select count(*) from (select unnest(arr)) as a)::text; return next;
exception when others then
get stacked diagnostics
v_sqlstate = returned_sqlstate,
v_message = message_text;
z := ''; return next;
z := v_sqlstate; return next;
z := v_message; return next;
end;
$body$;
\sf+ f()
select f();
"create or replace" succeeds and "select" reports what I expect: 2. Now if I change "arr" to "art", I get the error report from my "others" handler that I expect:
42703
column "art" does not exist
If I fix "art" back to "arr" and change "v_message" in "z := v_message; return next;" to "q_message", then "create or replace" succeeds—very much to my surprise. Moreover; "select" succeeds too—presumably because the point of execution never enters the "others" handler. Only if (with the "q_message"" typo still in place) I change "arr" to "art" again, do I get this error on "select":
column "q_message" does not exist
Is this expected? In other words, is there a careful explanation of what "parse" means in the context of "create or replace" for a subprogram that predicts all of the outcomes that I reported here?
Or might my final observation be considered to be a bug—and if so, might it be fixed?
