Postgres 13, new family of polymorphic types - anycompatible

One very interesting PostgerSQL's feature are polymorphic types. It's interesting mainly for people who writes extensions.

Example: I can write own isnull function:

CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(NULL, 1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, CURRENT_DATE);
┌────────────┐
│   isnull   │
╞════════════╡
│ 2020-03-31 │
└────────────┘
(1 row)

-- but
postgres=# SELECT public.isnull(1, 1.1);
ERROR:  function public.isnull(integer, numeric) does not exist
LINE 1: SELECT public.isnull(1, 1.1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

It is working for all supported types. One and big limit of type family any* is rigidity. It is working for all types, but for previous example, first and second parameter has to have exactly same types. Similar buildin functions are more tolerant - usually requires same type category only. This limit should be removed with new family of polymorphic types anycompatible. When there are more arguments with this type, then most common type is calculated and all arguments of anycompatible are casted to selected most common type:

CREATE OR REPLACE FUNCTION isnull(anycompatible, anycompatible)
RETURNS anycompatible AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(1, 1.1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, 1.1);
┌────────┐
│ isnull │
╞════════╡
│    1.1 │
└────────┘
(1 row)

Now, it is working as expected.

This feature is interesting mainly for authors of extensions that has to emulate some different databases (like Orafce or similar).

pspg 2.7.0 with inotify support

Long time I am playing with psql and I try to enhance and fix some limits of this client. I like it. The work with this client is pretty fast, and with pspg the result's browsing is almost comfortable. But still there is disadvantage - is not possible to see result and query in one time, and isn't possible to work with more results in one time. But with new inotify support these limits are removed. You can create file that will be used as input/output buffer. Now start pspg with this file. When this file will be changed (by psql), then pspg reread this file immediately.

[pavel@nemesis ~]$ touch ~/r1
[pavel@nemesis ~]$ touch ~/r2
[pavel@nemesis ~]$ touch ~/r3

Then start in three terminals pspg (I use old terminator or new tilix)

[pavel@nemesis ~]$ pspg -f ~/r1
[pavel@nemesis ~]$ pspg -f ~/r2
[pavel@nemesis ~]$ pspg -f ~/r3

and now, you can play

psql
postgres=# select * from pg_class \g ~/r1
postgres=# select * from pg_proc \g ~/r2
postgres=# select * from pg_database \g ~/r3

And I see queries and results

small update of ncurses-st-menu

ncurses-st-menu is small library over ncurses for pulldown and menubar menu. Stripped size of library has 43kB, so it is not extra light, but it is in category smaller libraries. One use case, that I would to support, is using in smaller application on smaller devices. For this purpose this library supports scrollable menu now. When the content of menu is longer then display, then the content will be scrollable - so cursor will be visible every time.

private, public plpgsql functions

Lot of people does migration from Oracle's PL/SQL to PostgreSQL's PL/pgSQL. One question is how to migrate private functions? There is not a reply. Oracle's PL/SQL is little bit modified language ADA. This language is procedural and modular. The concept of private functions or public functions is very natural there. Nothing similar is in Postgres. Not in this moment. PLpgSQL functions are called by SQL API every time. So visibility for SQL engine should be same for all functions. Theoretically anybody case set SEARCH_PATH for any function manually and then functions from other schema will be invisible. But this design is pretty frail. Somebody can call functions from other schema by qualified name, and the often switch of SEARCH_PATH in significantly decrease readability of your code.

I think so some convention can help. For example, we can mark of any private functions by prefix '_'. With plpgsql_check we can check if these functions are used only inside functions with same schema. The plpgsql_check's function plpgsql_show_dependency_tb does this work.

CREATE SCHEMA packagex;

/*
 * private function, should be used only by functions from this schema
 */
CREATE OR REPLACE FUNCTION packagex._privatefx(a integer)
RETURNS integer AS $$
BEGIN
  RETURN a;
END;
$$ LANGUAGE plpgsql;

/*
 * public function - use private function correctly
 */
CREATE OR REPLACE FUNCTION packagex.publicf1(a integer)
RETURNS integer AS $$
BEGIN
  RETURN packagex._privatefx(a);
END;
$$ LANGUAGE plpgsql;

/*
 * public function - doesn't use private function correctly
 */
CREATE OR REPLACE FUNCTION public.publicf2(a integer)
RETURNS integer AS $$
BEGIN
  RETURN packagex._privatefx(a);
END;
$$ LANGUAGE plpgsql;

The function public.publicf2 is breaking our convention and I have to identify this function.

SELECT *
  FROM (SELECT pronamespace::regnamespace,
               proname,
               x.*
          FROM pg_proc,
               plpgsql_show_dependency_tb(oid) x
         WHERE prolang = (SELECT oid 
                            FROM pg_language 
                           WHERE lanname = 'plpgsql')
           AND pronamespace <> 'pg_catalog'::regnamespace) s
 WHERE starts_with(name, '_')
   AND schema::regnamespace <> pronamespace;

┌──────────────┬──────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ pronamespace │ proname  │   type   │  oid  │  schema  │    name    │  params   │
╞══════════════╪══════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ public       │ publicf2 │ FUNCTION │ 16411 │ packagex │ _privatefx │ (integer) │
└──────────────┴──────────┴──────────┴───────┴──────────┴────────────┴───────────┘
(1 row)

There is not nice possibility how to implement private functions in Postgres now, but we can introduce some conventions and we can check if these conventions are broken or not.

The function plpgsql_show_dependency_tb display all objects used by function:

SELECT pg_proc.oid::regprocedure AS function, 
       x.oid::regprocedure AS "called function"
  FROM pg_proc,
       plpgsql_show_dependency_tb(oid) x
 WHERE prolang = (SELECT oid 
                    FROM pg_language 
                   WHERE lanname = 'plpgsql')
   AND pronamespace <> 'pg_catalog'::regnamespace 
   AND x.type = 'FUNCTION';
┌────────────────────────────┬──────────────────────────────┐
│          function          │       called function        │
╞════════════════════════════╪══════════════════════════════╡
│ packagex.publicf1(integer) │ packagex._privatefx(integer) │
│ publicf2(integer)          │ packagex._privatefx(integer) │
└────────────────────────────┴──────────────────────────────┘
(2 rows)