When you develop functions or procedures in PostgreSQL’s PL/pgSQL there are some points to consider, which make your life easier. This post is not about control structures, nor is it about development in general. This is more about small things to know, especially if you just started with PostgreSQL and PL/pgSQL.

Consider this function:

postgres=# create or replace function f1() returns void as
$$
begin
  select * from t1;
end; $$ language plpgsql;
CREATE FUNCTION

PostgreSQL will create the function without any issues, but as soon as you call the function, you’ll notice that you get an error, because the table “t1” does not exist:

postgres=# select f1();
ERROR:  relation "t1" does not exist
LINE 1: select * from t1
                      ^
QUERY:  select * from t1
CONTEXT:  PL/pgSQL function f1() line 3 at SQL statement

This means, PostgreSQL does not check if referenced objects in the body of the function do really exist at the time the function is created. It is, however, checking if the data type you want to return does really exist:

postgres=# create or replace function f2() returns my_type as
$$
begin
  select * from t1;
end; $$ language plpgsql;
ERROR:  type "my_type" does not exist

I’ve covered that in more detail some time ago here.

Another point to consider is, that you can enable additional Compile-Time and Run-Time checks. Lets have a look a the following function:

postgres=# create or replace function f3(p1 int) returns int as
$$
declare
  p1 int := 1;
begin
  return p1;
end; $$ language plpgsql;
CREATE FUNCTION

If we call the function like this:

postgres=# select * from f3(2);

… what will it return, one or two? The parameter which goes into the function has the same name as the variable in the function. 

postgres=# select * from f3(2);
 f3 
----
  1
(1 row)

This is called shadowing of variables and you can tell PostgreSQL to check for this:

postgres=# set plpgsql.extra_warnings to 'shadowed_variables';
SET
postgres=# create or replace function f3(p1 int) returns int as
$$
declare
  p1 int := 1;
begin
  return p1;
end; $$ language plpgsql;
WARNING:  variable "p1" shadows a previously defined variable
LINE 4:   p1 int := 1;
          ^
CREATE FUNCTION
postgres=#

Lets have a look at another example:

postgres=# create table t ( a int, b int );
CREATE TABLE
postgres=# insert into t values (1,1);
INSERT 0 1
postgres=# create function f4() returns void as
$$
declare
  v1 int;
  v2 int;
begin
  select a,b
    into v1,v2
    from t;
end; $$ language plpgsql;
CREATE FUNCTION

Executing this currently is not an issue as the table exactly contains one row:

postgres=# select * from f4();
 f4 
----
 
(1 row)

But what happens if there is more than one row in that table?

postgres=# insert into t values (2,2);
INSERT 0 1
postgres=# select * from f4();
 f4 
----
 
(1 row)

PostgreSQL will still execute the function, but how can you know which row you get? If you have an “into” statement and there will be potentially more than row returned, this is probably wrong and you can ask PostgreSQL to warn you about that:

postgres=# set plpgsql.extra_warnings to 'too_many_rows';
SET
postgres=# select * from f4();
WARNING:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
 f4 
----
 
(1 row)

postgres=#

Last example for today: Consider this:

postgres=# create or replace function f5() returns void as
$$
declare
  v1 int;
  v2 int;
begin
  select a,b,'aaa'
    into v1,v2
    from t;
end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select f5();
WARNING:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
 f5 
----
 
(1 row)

We still get the warning from before, but what happens to the third value we’ve selected? PostgreSQL will just ignore it, but you also can enable a warning for this:

postgres=# set plpgsql.extra_warnings to 'strict_multi_assignment';
SET
postgres=# select f5();
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
 f5 
----
 
(1 row)

postgres=#

Combining these flags is possible as well:

postgres=# set plpgsql.extra_warnings to 'strict_multi_assignment','too_many_rows','shadowed_variables';
SET

At least for the time you spend on developing functions or procedures you should enable those.

Post Views: 4,109
Thumbnail [60x60]
by
Daniel Westermann