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)';
  ...

bugfix release of pspg - 5.3.5

you can download source code from https://github.com/okbob/pspg/releases/tag/5.3.5

orafce_mail

I wrote new Postgres's extension - orafce_mail. This extension enhancing an orafce about possibility to send mail from Postgres. I tested this extension against my gmail account, and it is work well. Just you need to generate special application's password, and that is all. This extension use library libcurl, and the features and possibilities of this extension is limitted by this library. It uses relativly new features of this library, so it cannot be linked with older versions of libcurl. The usage is simple:

You have to set configure variables:

set orafce_mail.smtp_server_url to 'smtps://smtp.gmail.com:465';
set orafce_mail.smtp_server_userpwd to 'pavel.stehule@gmail.com:yourgoogleapppassword';

After that you can send an mail without an attachment:

call utl_mail.send(sender => 'pavel.stehule@gmail.com',
                   recipients => 'pavel.stehule@gmail.com',
                   subject => 'ahoj, nazdar, žlutý kůň',
                   message => e'test, \nžlutý kůň');

or mail with an attachment:

do $$
declare
  myimage bytea = (select img from foo limit 1);
begin
  call utl_mail.send_attach_raw(sender => 'pavel.stehule@gmail.com',
                                recipients => 'pavel.stehule@gmail.com',
                                subject => 'mail with picture',
                                message => 'I am sending some picture',
                                attachment => myimage,
                                att_mime_type => 'image/png',
                                att_filename => 'screenshot.png');
end
$$;

The implemented API is almost compatible with Oracle's package utl_mail and dbms_mail. This extension requires PostgreSQL 10 and higher (with procedures support).