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

>
> Thank you very much indeed for this careful reply, Pavel—and for the links
> to the threads on the Hackers list. A great deal is now clarified for me.
> You said “I am not native speaker, and my English is very poor”. You’re
> far, far, too modest. I am a native English speaker. And I often see
> writing from other native English speakers that’s pretty awful. Your
> writing is a lot better than theirs. Of course, I don’t know a single word
> of Czech.
>

Thank you. Gmail auto correction (ai) does a lot of good work :-)

> I’m glad to see that the non-transactional behavior is still part of the
> plan—even if, later, a transactional variant is added. I’m glad to see,
> too, that READ/WRITE privileges and IMMUTABLE are now part of the plan.
>
> Just a detail, now. You sad this:
>
> variables have their own namespace, because they have their own catalog
> table. It is designed like any catalog object - so you can use (or you
> don't need) to use a qualified identifier. But there is not any schema
> scope
>
>
> I probably misused the phrase “schema scope”. I meant only to say that, if
> two schemas, s1 and s2, exist in the same database, then it’s OK to have
> two session variables called s1.x and s2.x. And you confirmed that this is
> the case. It seems that you can also have a session variable called s1.x
> and a table called s1.x. In a separate reply to my email,
> david(dot)g(dot)johnston(at)gmail(dot)com thought that this would be disallowed because
> session variables would share the same relations namespace that tables and
> views already do. (I have no opinion here.) Thanks for the clarification.
>

There are more possible collisions than like the mentioned. You can have
composite variable x with field y, and you can have schema x with table y -
and then what is x.y in a query when you don't know context? Good news is
fact, so we know context - and we know so FROM clause can contain just
table identifiers (so some collisions can be solved automatically with
context knowledge) . Fortunately, implicit FROM clause functionality was
removed a few years ago.

Inside the patch is a routine that calculates possible applications of
identifiers or quoted identifiers. When there is more than one valid
interpretation, the exception about ambiguous identifiers is raised.

Unfortunately a new badly named session variable can break working queries,
but this is not a new situation in SQL. New badly named column of table can
do this break too.

postgres=# create variable x as int default 10;
CREATE VARIABLE
postgres=# create table x (a int);
CREATE TABLE
postgres=# select * from x;
┌───┐
│ a │
╞═══╡
└───┘
(0 rows)

postgres=# select x.a from x;
┌───┐
│ a │
╞═══╡
└───┘
(0 rows)

postgres=# select x, x.a from x;
ERROR: column reference "x" is ambiguous (note - there is collision with
hidden column x of table x)
LINE 1: select x, x.a from x;
^
DETAIL: The qualified identifier can be column reference or session
variable reference
postgres=# select public.x, x.a from x;
┌───┬───┐
│ x │ a │
╞═══╪═══╡
└───┴───┘
(0 rows)

postgres=# insert into x values(1000);
INSERT 0 1
postgres=# select public.x, x.a from x;
┌────┬──────┐
│ x │ a │
╞════╪══════╡
│ 10 │ 1000 │
└────┴──────┘
(1 row)

> All is clear about collisions and name qualification now. And, yes: I
> agree with your advice to leave those settings that you mentioned at their
> shipped defaults and to go with the native behavior (collisions simply
> cause a run-time error). Then to fix such errors by ordinary spelling
> changes (esp. schema qualification) in one’s code.
>
> You said “I invite any help with code and documentation review”. I’m not a
> C coder. But I’d be happy to help with documentation review when the time
> comes.
>

documentation check or just check in code comments can be more than good
enough (or testing), or just voice in discussion in the mailing list.

> Finally, I’m well advanced with my promised mini-project to prepare and
> explain a realistic use-case to show the benefits of a package construct in
> PL/pgSQL. I’ll wait until after Xmas before I share it with the General
> list. Having said this, I already appreciate that the chances of bringing
> packages to some future PG release are vanishingly small.
>

I am sure packages have some advantages - this is an important feature of
ADA language. The possibility of private objects is important and
interesting. Possibility to sharing code is interesting too.

But Postgres already has schemas (a little bit different from Oracle) and
extensions. And internal implementation of PL/pgSQL disallow any sharing
across databases. So introduction of packages to Postgres is introducing
some not trivial and partially redundant concept. Currently, Postgres is
relatively small and very very consistent software - and I believe so is
one of the reasons why Postgres is popular. It is easy to learn, easy to
use. The internal complexity is well solved and hidden. This is a long goal
for community Postgres. The compatibility with Oracle should not be
important after 20 years (although it is very important for a lot of
current users and for users who can leave Oracle). If we miss some feature
in Postgres, we should to implement it, but with respect to current
features.

Regards

Pavel