pspg 5.5.0

 Today I released pspg 5.5. There are few new interesting features:

• stream mode based on continuous reading from file (uses kqueue) is supported on BSD Unix,
• There are two new visual effects - possibility to hide border line, and possibility to highlight odd rows
• pspg can be used in Oracle's SQLcl client too. It should to work with default format and with ANSICONSOLE format,
• one char vertical scrolling is supported too. 

 

Orafce 3.17.0

I released Orafce 3.17.0. This is bugfix only release. Giles Darold wrote a patch that fixes orafce's regexp functions for NULL arguments.

pspg 5.4.1 released

I released bugfix release of pspg. The problem with copy to clipboard on macos (using by pbcopy) is fixed in this release.

Orafce 3.16.2 for Postgres 12, 13 and 14 for Microsoft Windows

I made dll of orafce 3.16.2. These dll are available from archive orafce-3.16.2-x64-win.zip Instalation is simple. Copy related dll file to directory Program files/Postgres/version/lib and rename this dll just to orafce. Next, copy orafce.control and orafce--3.16.sql to Program files/Postgres/version/share/extension. Last step is executing command CREATE EXTENSION orafce in the database, where you want to use orafce. It can require redistributable runtime for Visual Studio 2019. Usually an good idea is set SEARCH_PATH to include schema oracle.

plpgsql_check 2.0.4 for Microsoft Windows and Postgres 12, 13 and 14.

I made dll of plpgsql_check 2.0.4. These dll are available from archive plpgsql_check-2.0.4-x64-win.zip Instalation is simple. Copy related dll file to directory Program files/Postgres/version/lib and rename this dll just to plpgsql_check. Next, copy plpgsql_check.control and plpgsql_check-2.0.sql to Program files/Postgres/version/share/extension. Last step is executing command CREATE EXTENSION plpgsql_check in the database, where you want to use plpgsql_check. It can require redistributable runtime for Visual Studio 2019.

pspg 5.4.0

I released pspg version 5.4.0.. In this release there is an possibility to customize buildin color themes:

template = 1
template_menu = 3

background = black, white
data = black, white
label = black, white, italic, bold
border = #000000, white
footer = lightgray, white
cursor_data = blue, white, italic, bold, dim, reverse
cursor_border = blue, blue , italic, bold, dim, reverse
cursor_label = blue, white, italic, bold, dim, reverse
cursor_footer = blue, white, italic, bold, dim, reverse
cursor_bookmark = red, white, italic, bold, dim, reverse
cross_cursor = white, blue, italic, bold
cross_cursor_border = brightblue, blue
status_bar = black, lightgray
title = black, lightgray
scrollbar_arrows = black, white
scrollbar_background = lightgray, white
scrollbar_slider = white, gray 

plpgsql_check 2.0.1

I released new significant version of plpgsql_check - plpgsql_check 2.0.1. Although there are only two new features (and few bugfixes), these two features are important.

I wrote about benefits of plpgsql_check for PL/pgSQL language developers in my blog Why you need plpgsql_check (if you write procedures in PLpgSQL). The plpgsql_check is PostgreSQL extensions, that does static analyze of PL/pgSQL code. It can detect lot of possible runtime bugs before execution, it can detect some performance or security isues too. More plpgsql_check can do coverage analyze, and has integrated profiler and tracer.

The PL/pgSQL language is relative static type strict language, and then the static analyze is working well. But there are two limits. The statis analyze cannot to work with objects and values that are created (calculated) at runtime. These objects are local temporary tables (PostgreSQL doesn't support global temporary tables yet) and the results of dynamic SQL:

postgres=# \sf+ fx1
        CREATE OR REPLACE FUNCTION public.fx1(tablename text)
         RETURNS void
         LANGUAGE plpgsql
1       AS $function$
2       DECLARE r record;
3       BEGIN
4         EXECUTE format('SELECT * FROM %I', tablename) INTO r;
5         RAISE NOTICE 'id=%', r.id;
6       END;
7       $function$

postgres=# SELECT * FROM plpgsql_check_function('fx1');
┌──────────────────────────────────────────────────────────────────────────────────────┐
│                                plpgsql_check_function                                │
╞══════════════════════════════════════════════════════════════════════════════════════╡
│ warning:00000:4:EXECUTE:cannot determinate a result of dynamic SQL                   │
│ Detail: There is a risk of related false alarms.                                     │
│ Hint: Don't use dynamic SQL and record type together, when you would check function. │
│ error:55000:5:RAISE:record "r" is not assigned yet                                   │
│ Detail: The tuple structure of a not-yet-assigned record is indeterminate.           │
│ Context: SQL expression "r.id"                                                       │
└──────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

postgres=# \sf+ fx2
        CREATE OR REPLACE FUNCTION public.fx2()
         RETURNS void
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3         CREATE TEMP TABLE IF NOT EXISTS ltt(a int);
4         DELETE FROM ltt;
5         INSERT INTO ltt VALUES(10);
6       END;
7       $function$

postgres=# SELECT * FROM plpgsql_check_function('fx2');
┌───────────────────────────────────────────────────────────┐
│                  plpgsql_check_function                   │
╞═══════════════════════════════════════════════════════════╡
│ error:42P01:4:SQL statement:relation "ltt" does not exist │
│ Query: DELETE FROM ltt                                    │
│ --                 ^                                      │
└───────────────────────────────────────────────────────────┘
(3 rows)

In plpgsql_check 2.0.1 I can use pragmas TYPE and TABLE (note: an syntax of pragma in plpgsql_check is little bit strange, because the language PL/pgSQL doesn't support native syntax for pragma (custom compiler directive) (like ADA language or PL/SQL language):

        CREATE OR REPLACE FUNCTION public.fx1(tablename text)
         RETURNS void
         LANGUAGE plpgsql
1       AS $function$
2       DECLARE r record;
3       BEGIN
4         PERFORM plpgsql_check_pragma('TYPE: r (id int)');
5         EXECUTE format('SELECT * FROM %I', tablename) INTO r;
6         RAISE NOTICE 'id=%', r.id;
7       END;
8       $function$

postgres=# SELECT * FROM plpgsql_check_function('fx1');
┌────────────────────────┐
│ plpgsql_check_function │
╞════════════════════════╡
└────────────────────────┘
(0 rows)

postgres=# \sf+ fx2
        CREATE OR REPLACE FUNCTION public.fx2()
         RETURNS void
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3         CREATE TEMP TABLE IF NOT EXISTS ltt(a int);
4         PERFORM plpgsql_check_pragma('TABLE: ltt (a int)');
5         DELETE FROM ltt;
6         INSERT INTO ltt VALUES(10);
7       END;
8       $function$

postgres=# SELECT * FROM plpgsql_check_function('fx2');
┌────────────────────────┐
│ plpgsql_check_function │
╞════════════════════════╡
└────────────────────────┘
(0 rows)

Note: if you use plpgsql_check 2.0.2, then you can use shorter form for PRAGMA:

BEGIN
  CREATE TEMP TABLE IF NOT EXISTS ltt(a int);
  PERFORM 'PRAGMA:TABLE: ltt (a int)';
  ...