Search Postgresql Archives

« The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



The subject line is copied from "PL/pgSQL under the Hood" (https://www.postgresql.org/docs/current/plpgsql-implementation.html). It implies the question:

« What does the term "parse" mean? »

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?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux