Database

plpgsql_check: PL/pgSQL Linter

plpgsql_check is a PostgreSQL extension that lints plpgsql for syntax, semantic and other related issues. The tool helps developers to identify and correct errors before executing the code. plpgsql_check is most useful for developers who are working with large or complex SQL codebases, as it can help identify and resolve issues early in the development cycle.

Enable the extension

API

plpgsql_check_function is highly customizable. For a complete list of available arguments see the docs

Usage

To demonstrate plpgsql_check we can create a function with a known error. In this case we create a function some_func, that references a non-existent column place.created_at.


_19
create table place(
_19
x float,
_19
y float
_19
);
_19
_19
create or replace function public.some_func()
_19
returns void
_19
language plpgsql
_19
as $$
_19
declare
_19
rec record;
_19
begin
_19
for rec in select * from place
_19
loop
_19
-- Bug: There is no column `created_at` on table `place`
_19
raise notice '%', rec.created_at;
_19
end loop;
_19
end;
_19
$$;

Note that executing the function would not catch the invalid reference error because the loop does not execute if no rows are present in the table.


_10
select public.some_func();
_10
some_func
_10
───────────
_10
_10
(1 row)

Now we can use plpgsql_check's plpgsql_check_function function to identify the known error.


_10
select plpgsql_check_function('public.some_func()');
_10
_10
plpgsql_check_function
_10
------------------------------------------------------------
_10
error:42703:8:RAISE:record "rec" has no field "created_at"
_10
Context: SQL expression "rec.created_at"

Resources