Horton

Re: Schema variables - new implementation for Postgres 15

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: walther@technowledgy.de, Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Dmitry Dolgov <9erthalion6@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Sergey Shinderuk <s.shinderuk@postgrespro.ru>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Julien Rouhaud <rjuju123@gmail.com>, dean.a.rasheed@gmail.com, er@xs4all.nl, joel@compiler.org, pgsql-hackers@lists.postgresql.org
Date: 2024-06-05T05:21:56Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow underscores in integer and numeric constants.

  2. Remove special outfuncs/readfuncs handling of RangeVar.catalogname.

  3. Remove extra space from dumped ALTER DEFAULT PRIVILEGES.

  4. Create FKs properly when attaching table as partition

  5. psql: improve tab-complete's handling of variant SQL names.

> 6. Oracle
>
> Oracle PL/SQL allows the use of package variables. PL/SQL is +/- ADA
> language - and package variables are "global" variables. They are not
> directly visible from SQL, but Oracle allows reduced syntax for functions
> without arguments, so you need to write a wrapper
>
> CREATE OR REPLACE PACKAGE my_package
> AS
>     FUNCTION get_a RETURN NUMBER;
> END my_package;
> /
>
> CREATE OR REPLACE PACKAGE BODY my_package
> AS
>     a  NUMBER(20);
>
>     FUNCTION get_a
>     RETURN NUMBER
>     IS
>     BEGIN
>       RETURN a;
>     END get_a;
> END my_package;
>
> SELECT my_package.get_a FROM DUAL;
>
> Inside SQL the higher priority has SQL, inside non SQL commands like CALL
> or some PL/SQL command, the higher priority has packages.
>

The risk of collision's identifier is in some PL/SQL statements less than
in Postgres, because SQL can be used only on dedicated positions (minimally
in older Oracle's versions). Against other databases there is not allowed
to use SQL everywhere as an expression. PL/SQL is an independent language,
environment with its own expression executor (compiler). Other databases
allow you to use an SQL subselect (I tested MySQL,  PL/pgSQL, and I think
(if I remember docs well) it is in standard SQL/PSM (related part of
ANSI/SQL)) as expression. The integration of SQL into PL/SQL is not too
deep and stored procedures look more like client code executed on the
server side.

Regards

Pavel