CODEWITHSUNDEEP
performancestored-proceduresplpgsql
Rama Swamy
Rama Swamy

Reputation: 23

Is static sql to be preferred over dynamic sql in postgresql stored procedures?

I am not sure in case of Stored Procedures, if Postgresql treats static sql any differently from a query submitted as a quoted string.

When I create a stored procedure in PostgreSQL using static sql, there seems to be no validation of the table names and table columns or column types but when I run the procedure I get the listing of the problems if any. 

open ref_cursor_variable for
select usr_name from usres_master;

-- This is a typing mistake. The table name should be users_master. But the stored procedure is created and the error is thrown only when I run the procedure.

When I run the procedure I (naturally) get some error like :

table usres_master - invalid table name

The above is a trivial version. The real procedures we use at work combine several tables and run to at least a few hundred lines. In PostgresQL stored procedure, is there no advantage to using static sql over dynamic sql i.e. something like open ref_cursor_variable for EXECUTE select_query_string_variable.

Upvotes: 2

Views: 851

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45940

The static SQL should be preferred almost time - dynamic SQL should be used only when it is necessary

  • from performance reasons (dynamic SQL doesn't reuse execution plans). One shot plan can be better some times (and necessary).
  • can reduce lot of code

In other cases uses static SQL every time. Benefits:

  • readability
  • reuse of execution plans
  • it is safe against SQL injection by default
  • static check is available

Upvotes: 2

user330315
user330315

Reputation:

The source of a function is just a string to Postgres. The main reason for this is the fact that Postgres (unlike other DBMS) supports many, even installable languages for functions and procedures. As the Postgres core can't possibly know the syntax of all languages, it can not validate the "inner" part of a function. To my knowledge the "language API" does not contain any "validate" method (in theory this would probably be possible though).

If you want to statically validate your PL/pgSQL functions (and procedures since Postgres 11) you could use e.g. https://github.com/okbob/plpgsql_check/

Upvotes: 0

Related Questions