How to split string to array by individual characters?

Postgres has too many features, so sometimes is good to remind some.

Function string_to_array is well known. This function has two or three parameters. If second parameter (delimiter) is null, then input string is separated to array of characters.

postgres=# select string_to_array('Pavel Stěhule',null);
┌───────────────────────────────┐
│        string_to_array        │
╞═══════════════════════════════╡
│ {P,a,v,e,l," ",S,t,ě,h,u,l,e} │
└───────────────────────────────┘
(1 row)

compiled dll of plpgsql 1.6 for PostgreSQL 10, 11

Adam Bartoszewicz prepared dll. Please, read a message.

Thank you, Adam

pspg is available in Fedora 29, Fedora 30 repository

If you use fresh Fedora distribution, you can install a pspg pager very simply:

dnf install pspg

after this:

export PSQL_PAGER=pspg #for Postgres 11 
export PAGER=pspg
psql dbname

don't use SQL keywords as PLpgSQL variable names

Yesterday I had a possibility to see some strange runtime error

CREATE OR REPLACE FUNCTION public.fx()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE offset int DEFAULT 0;
BEGIN
  RETURN offset + 1;
END;
$function$

postgres=# SELECT fx();
ERROR:  query "SELECT offset + 1" returned 0 columns
CONTEXT:  PL/pgSQL function fx() line 4 at RETURN

What is problem? On first view, the RETURN returns 1 column, so error message is strange.

But any PLpgSQL expression is a SQL expression - more it is SQL SELECT statement. So SELECT OFFSET 1 really returns 0 columns.

The basic issue is on bad name of variable - it is same like SQL reserved keyword. OFFSET is unhappy word.

I wrote new check to plpgsql_check, that raises a warning in this situation.

plpgsql_check - new report for code coverage ratio calculation

Few months ago I integrated a profiler into plpgsql_check.

The result of prifiling is line oriented:

postgres=# select lineno, avg_time, source from plpgsql_profiler_function_tb('fx(int)');
┌────────┬──────────┬───────────────────────────────────────────────────────────────────┐
│ lineno │ avg_time │                              source                               │
╞════════╪══════════╪═══════════════════════════════════════════════════════════════════╡
│      1 │          │                                                                   │
│      2 │          │ declare result int = 0;                                           │
│      3 │    0.075 │ begin                                                             │
│      4 │    0.202 │   for i in 1..$1 loop                                             │
│      5 │    0.005 │     select result + i into result; select result + i into result; │
│      6 │          │   end loop;                                                       │
│      7 │        0 │   return result;                                                  │
│      8 │          │ end;                                                              │
└────────┴──────────┴───────────────────────────────────────────────────────────────────┘
(9 rows)

This format is well readable, but it is not practical for calculation of code coverage metrics. So this week I wrote new function, that produce
report based on commands:

        CREATE OR REPLACE FUNCTION public.fx1(a integer)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       begin
3         if a > 10 then
4           raise notice 'ahoj';
5           return -1;
6         else
7           raise notice 'nazdar';
8           return 1;
9         end if;
10      end;
11      $function$

postgres=# select stmtid, parent_stmtid, parent_note, lineno, exec_stmts, stmtname
             from plpgsql_profiler_function_statements_tb('fx1');
┌────────┬───────────────┬─────────────┬────────┬────────────┬─────────────────┐
│ stmtid │ parent_stmtid │ parent_note │ lineno │ exec_stmts │    stmtname     │
╞════════╪═══════════════╪═════════════╪════════╪════════════╪═════════════════╡
│      0 │             ∅ │ ∅           │      2 │          0 │ statement block │
│      1 │             0 │ body        │      3 │          0 │ IF              │
│      2 │             1 │ then body   │      4 │          0 │ RAISE           │
│      3 │             1 │ then body   │      5 │          0 │ RETURN          │
│      4 │             1 │ else body   │      7 │          0 │ RAISE           │
│      5 │             1 │ else body   │      8 │          0 │ RETURN          │
└────────┴───────────────┴─────────────┴────────┴────────────┴─────────────────┘
(6 rows)

Now, it should not be too difficult to calculate (by SQL) some coverage metrics.

New release of Orafce extension

I released new mostly release of Orafce. Today it is massive package of emulation often used Oracle's API and the emulation is on maximum what is possible.

Now Orafce has good and very nice documentation written by Horikawa Tomohiro (big thanks for his work).

There are not too much news for people who use Oracle 3.6:

• possibility to better emulate || operator for varchar2 and nvarchar2 types
• few bugfixes
• only PostgreSQL 9.4 and newer are supported
• support for PostgreSQL 11, current master branch (future PostgreSQL 12) is supported too

plpgsql_check can detect bad default volatility flag

Common performance problem of plpgsql function when these functions are used from some more complex queries is using default VOLATILE flag. There are not possible to do more aggressive optimization of this function call. plpgsql_check can detect this issue now:

CREATE OR REPLACE FUNCTION public.flag_test1(integer)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
begin
  return $1 + 10;
end;
$function$;

CREATE OR REPLACE FUNCTION public.flag_test2(integer)
 RETURNS integer
 LANGUAGE plpgsql
 VOLATILE
AS $function$
begin
  return (select * from fufu where a = $1 limit 1);
end;
$function$;

postgres=# select * from plpgsql_check_function('flag_test1(int)', performance_warnings => true);
┌────────────────────────────────────────────────────────────────────┐
│                       plpgsql_check_function                       │
╞════════════════════════════════════════════════════════════════════╡
│ performance:00000:routine is marked as STABLE, should be IMMUTABLE │
└────────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# select * from plpgsql_check_function('flag_test2(int)', performance_warnings => true);
┌──────────────────────────────────────────────────────────────────────┐
│                        plpgsql_check_function                        │
╞══════════════════════════════════════════════════════════════════════╡
│ performance:00000:routine is marked as VOLATILE, should be STABLE    │
└──────────────────────────────────────────────────────────────────────┘
(1 row)