We started to use Postgres much more recently, having moved from SQL Server. I've noticed that Postgres parser/compiler allows creation of functions that (it seems to me) can be rejected at creation time.
One example of what I'm talking about is select statements in plpgsql blocks:
create or replace function test() returns void as $$
begin
select * from pg_database;
end;
$$ language plpgsql;
This function fails at runtime with "query has no destination for result data". Why would this error not be caught at function creation time? Is there any case when using select without 'return' in plpgsql block is allowed?
The other type of errors that are not always caught at compile time is type mismatch errors between a declared return type and actual type of the value. These are caught in simple cases, but start to make it to runtime in more complicated functions. I suspect there's some limitations in Postgres type inference/analysis, is there any additional information on this available?
tldr: Is there any way to make Postgres parser/compiler fail more on function creation?
Is there any way to make Postgres parser/compiler fail more on function creation?
That's the purpose of the plpgsql_check extension. It won't prevent the function to be created, though.
test=# create extension plpgsql_check;
CREATE EXTENSION
test=# create or replace function test() returns void as $$
begin
select * from pg_database;
end;
$$ language plpgsql;
test=# select * from plpgsql_check_function('test');
plpgsql_check_function
----------------------------------------------------------------------
error:42601:3:SQL statement:query has no destination for result data