Monday, August 14, 2017

short update: please, test pspg

lot of done, lot of code rewritten

please, test it - https://github.com/okbob/pspg.

Friday, August 4, 2017

PostgreSQL pager pspg is ready to work

I spent lot of time on work on pspg. These points are done:
  • support for expanded mode
  • fixed resizing
  • two new styles
  • start is significantly faster
  • lot of display errors was fixed
 This code should be compiled from source code. If you want to test it - you need develop packages of ncursesw. If you don't need wide char support, you can compile pspg against ncurses library (in this case a Makefile should be modified).

Usage:
\setenv PAGER 'pspg -s 2'
\pset pager always
select * from pg_stat_activity;

Sunday, July 16, 2017

Postgres Pager (pager designed for psql)

I hope so every, who uses psql uses less pager as default pager. See important Merlin's article. less is great pager, but it is not designed for usage as pager of relational data, and miss some features. Mainly, there is not possible to freeze some rows and some columns. I didn't find any solution good enough for me, although there are some projects on github. The special pager for psql should to have some logic, to choose correctly and automatically frozen columns (in chars) and rows.

I wrote new pager pspg - Postgres pager. This pager is designed primary for usage as psql pager. Now, it is available for playing and testing. Probably there will be some unwanted artefacts - but for usual work it is good enough. It is first my ncurses applications, probably there will be some bugs.

This pager currently supports:
* possibility to set one from three themes from command line: -s 0 - black/white theme, -s 1 mc theme, -s 2 fox theme,
* possibility to set frozen columns from command line: -c N or interactively (0..4),
* first few rows with labels are frozen automatically,
* scrolling: left, rights, up, down, PgUp, PgDn, ^PgUp, ^PgDn,
* possibility to run demo from command line: -d,
* supports different psql border linestyles: ascii, unicode,
* supports different psql border levels: 0, 1, 2

Usage:
export PAGER="pspg" #default theme 1
psql dbname
\pset border 2 #best works with border 2, but others are supported too
or
export PAGER="pspg -s 0" #black white theme
psql dbname
..

  • black white theme -s 0
  • midnight commander theme -s 1
  • fox theme -s 2


I invite any cooperation with people with ncurses knowleadge. All patches are welcome.

Wednesday, July 5, 2017

new packages in community repository

I wrote two small applications pgimportdoc and pgexportdoc. Now these applications are available from community repository (Thanks to Devrim GÜNDÜZ).

These applications should be used for import/export long documents (text, json, jsonb, xml, bytea) to/from Postgres.

[pavel@localhost ]$ ./pgimportdoc postgres -f ~/Stažené/enprimeur.xml -c 'insert into xmldata values($1)' -t XML
[pavel@localhost ]$ cat ~/Stažené/enprimeur.xml | ./pgimportdoc postgres -c 'insert into xmldata values($1)' -t XML
[pavel@localhost ]$ cat ~/Stažené/enprimeur.xml | ./pgimportdoc postgres -E latin2 -c 'insert into doc values($1) returning id' -t TEXT

[pavel@localhost ]$ pgexportdoc -c 'select x from xmldata where id = 1' -t XML -f myxmldoc.xml

Tuesday, June 27, 2017

replace_empty_string

Last half of year I am working on migration relative big application from Oracle to Postgres. This application is based on intensive usage of stored procedures, triggers, views. The base tools are ora2pg and plpgsql_check. Many thanks to Gilles Darold for his work on ora2pg. Half year ago this tool has almost zero support for PL/SQL - and now it is able to translate 90% of big code base of old PL/SQL code to PLpgSQL. There was lot of issues, but often was fixed to next day. Thank you.

Some tools I had to write too. I have some points for Orafce. Last tool what I wrote for this project is replace_empty_string extension. Oracle doesn't save empty strings - it does translation to NULL implicitly. To ensure similar behave I wrote generic trigger, that any empty string replaces by NULL. Default is quite behave, but warning (when string is empty string) is possible.

Example:
CREATE EXTENSION replace_empty_string;

CREATE TABLE res (
 id  int4,
 idesc  text,
 test1  varchar,
 test2  text
);

CREATE TRIGGER res_replace_empty_string
 BEFORE UPDATE OR INSERT ON res
 FOR EACH ROW
 EXECUTE PROCEDURE replace_empty_string ();

INSERT INTO res VALUES (1, 'first', NULL, '');
INSERT INTO res VALUES (2, NULL, '', 'Hello');

\pset null ****

SELECT * FROM res;
 id | idesc | test1 | test2 
----+-------+-------+-------
  1 | first | ****  | ****
  2 | ****  | ****  | Hello
(2 rows)

UPDATE res SET idesc = ''
 WHERE id = 1;

SELECT * FROM res;
 id | idesc | test1 | test2 
----+-------+-------+-------
  2 | ****  | ****  | Hello
  1 | ****  | ****  | ****
(2 rows)

Wednesday, May 31, 2017

New version of PLpgSQL_check

Hi I released new version of plpgsql_check. Some bugs are fixed. There are new extra warnings related to function arguments and PostgreSQL 10 is supported now.
create or replace function fx_xt(out x xt, out y xt)
as $$
declare c1 xt; c2 xt;
begin
  return;
end;
$$ language plpgsql;
select * from plpgsql_check_function('fx_xt()');
                         plpgsql_check_function                         
------------------------------------------------------------------------
 warning:00000:2:DECLARE:unused variable "c1"
 warning:00000:2:DECLARE:unused variable "c2"
 warning extra:00000:composite OUT variable "$1" is not single argument
 warning extra:00000:unmodified OUT variable "$1"
 warning extra:00000:composite OUT variable "$2" is not single argument
 warning extra:00000:unmodified OUT variable "$2"
(6 rows)

Monday, April 10, 2017

Orafce and plpgsql_check are ready for PostgreSQL 10

Small note: All regress tests of https://github.com/orafce/orafce and https://github.com/okbob/plpgsql_check passed on PostgreSQL 10.