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
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "plpgsql_check" and enable the extension.
API
plpgsql_check_function( ... ): Scans a function for errors.
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.
_19create table place(_19 x float,_19 y float_19);_19_19create or replace function public.some_func()_19 returns void_19 language plpgsql_19as $$_19declare_19 rec record;_19begin_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;_19end;_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.
_10select 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.
_10select 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
- Official
plpgsql_checkdocumentation