New in PostgreSQL 9.3: Server Side languages

In series of blogging about new features coming in PostgreSQL 9.3, I thought to blog about the server side language improvements in PostgreSQL. Lets see whats coming in server side language.
As PostgreSQL user, you know, PostgreSQL supports multiple server side language. In 9.3, there are some interesting features are coming. Lets look at the new improvements about to come.

1. SPI access to number of rows processed by COPY command.

This is more like new feature which is introduced in 9.3. Before 9.3, this feature was missing. i.e if user uses COPY command inside the plperl/plpython functions, then there was no way inside the procedure to trace the number of rows processed by COPY. However in 9.3, this limitation is no more exists with procedural language. There are many languages supported in PostgreSQL, However I chose to test this with mostly used language plperl and plpython. Below are some snapshot pre-9.3 and in 9.3.
Lets check with plperl. Following is a plperl function which can be use:

Definition of table is given below:

   Table "public.test_copy"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | numeric | 

Content of data file:

cat /tmp/test.data
1
2
3
4
5

Following is a plperl function which can be use for testing in pre-9.3 and in 9.3

CREATE OR REPLACE FUNCTION test_copy() RETURNS integer
AS $$
    my $rv = spi_exec_query("COPY test_copy FROM '/tmp/test.data'");
    my $status = $rv->{status};
    my $nrows = $rv->{processed};
    return $nrows;
$$ LANGUAGE plperl;

If we execute COPY command on psql prompt, user will get message like given below:

worktest=# COPY test_copy from '/tmp/test.data';
COPY 5

which shows COPY has processed 5 rows and accordingly loaded in table.
If we use above plperl function in pre-9.3, user will get following result:

worktest=# select split_part(version(),' ',2) as version;
 version  
----------
 9.2.4.10
(1 row)

worktest=# select test_copy();
 test_copy 
-----------
         0
(1 row)

which shows function was not able to get the number of rows processed by COPY command. However if we use same plperl function in 9.3, we will get following result:

postgres=# select split_part(version(),' ',2) as version;
 version  
----------
 9.3beta2
(1 row)

postgres=# select test_copy();
 test_copy 
-----------
         5
(1 row)

which shows that plperl function in 9.3 is able to get the number of rows processed.

Similarly we can use following plpython function to test this new feature:

CREATE FUNCTION result_copy_test(cmd text) RETURNS int
AS $$
plan = plpy.prepare(cmd)
plpy.info(plan.status()) 
result = plpy.execute(plan)
return result.nrows()
$$ LANGUAGE plpythonu;

pre-9.3

worktest=#  SELECT result_copy_test($$ COPY test_copy FROM '/tmp/test.data' $$);
INFO:  True
CONTEXT:  PL/Python function "result_copy_test"
 result_copy_test 
------------------
                0
(1 row)

which shows function was not able to get the processed rows by COPY command.

In 9.3

postgres=# SELECT result_copy_test($$ COPY test_copy FROM '/tmp/test.data' $$);
INFO:  True
CONTEXT:  PL/Python function "result_copy_test"
 result_copy_test 
------------------
                5
(1 row)

which shows function was able to get the processed rows of COPY command.

2. Allow GET DIAGNOSTICS x = ROW_COUNT to access rows processed by COPY 

This is an enhancement in plpgsql, if user wants to access the rows processed by COPY command in plpgsql Block, then he can use GET DIAGNOSTICS variable = ROW_COUNT, which was not possible before 9.3.
To check with plpgsql, user can use either create a sample function or can use following anonymous block to verify this feature. I will be using DO block of plpgsql to verify this feature. Example is given below:

PL/pgSQL anonymous block

DO $$
  DECLARE
    r int; 
  BEGIN
     COPY test_copy FROM '/tmp/test.data';
     GET DIAGNOSTICS r = row_count;
     RAISE NOTICE 'processed rows => %',r;
  END;
$$ language plpgsql;

pre-9.3

worktest=# DO $$
worktest$#   DECLARE
worktest$#     r int; 
worktest$#   BEGIN
worktest$#      COPY test_copy FROM '/tmp/test.data';
worktest$#      GET DIAGNOSTICS r = row_count;
worktest$#      RAISE NOTICE 'processed rows => %',r;
worktest$#   END;
worktest$# $$ language plpgsql;
NOTICE:  processed rows => 0
DO

which shows pre-9.3 was not able to get processed rows.

In 9.3

postgres=# DO $$
postgres$#   DECLARE
postgres$#     r int; 
postgres$#   BEGIN
postgres$#      COPY test_copy FROM '/tmp/test.data';
postgres$#      GET DIAGNOSTICS r = row_count;
postgres$#      RAISE NOTICE 'processed rows => %',r;
postgres$#   END;
postgres$# $$ language plpgsql;
NOTICE:  processed rows => 5
DO
postgres=

In 9.3, plpgsql is able to get processed rows. Interesting.

3. Allow use of RETURN with a composite expression in PL/pgSQL.

This is new addition in PL/pgSQL and also useful for user who is reluctant to declare variable too much and wants RETURN in PL/pgSQL to return the expression.
This feature was not available pre-9.3 PL/pgSQL. However, in 9.3, user can use this easily. Below is an example.

create type footype as (x int, y varchar);

create or replace function foo() returns footype as $$
begin
  return (1, 'hello')::footype;
end;
$$ language plpgsql;

Lets try with pre-9.3 first , when user will try to create above function user will get following error message in pre-9.3:

worktest=# create or replace function foo() returns footype as $$
worktest$# begin
worktest$#   return (1, 'hello')::footype;
worktest$# end;
worktest$# $$ language plpgsql;
ERROR:  RETURN must specify a record or row variable in function returning row
LINE 3:   return (1, 'hello')::footype;

In 9.3, due to addition of above feature, this works great.

postgres=# create or replace function foo() returns footype as $$
postgres$# begin
postgres$#   return (1, 'hello')::footype;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 

And user can see the working output too:

postgres=# select foo();
    foo    
-----------
 (1,hello)
(1 row)

4. New in PL/Python 

There are some good improvement coming in plpython too. These improvement will be useful for users who like to make plpython and wants to debug the function or wants to print some useful information. This was missing in pre-9.3. Lets look at whats new in plptyhon.

i. Addition of object string in Pl/Python 

Before 9.3, whenever user wants to look at the information provided by object handler in Pl/Python, it doesn't used to give useful information. However, In 9.3, this is going to give some useful information which will be helpful for Pl/Python users.

Lets look at example, how this change is useful. Following is small code which can be use for testing this new addition:

CREATE FUNCTION test_debug_info() RETURNS text AS $$
    try:
        rv=plpy.execute("SELECT datname FROM pg_catalog.pg_database",5)
        plpy.info(rv);
    except plpy.SPIError:
        return "Not working"
    else:
        return "Working good"
$$ LANGUAGE plpythonu;

pre 9.3

worktest=# select test_debug_info() ;
INFO:  
CONTEXT:  PL/Python function "test_debug_info"
 test_debug_info 
-----------------
 Working good
(1 row)

Above user can see INFO hasn't given the very much useful information.
In 9.3

postgres=# select test_debug_info() ;
INFO:  
CONTEXT:  PL/Python function "test_debug_info"
 test_debug_info 
-----------------
 Working good
(1 row)

Above you can see INFO has given some useful information about status, number rows and rows return by the query. Very useful, if somebody wants to know what handler contains in Pl/Python.

ii. Conversion of OID values to proper Pl/Python numeric type 

Before 9.3, Database type OID used to be treated as string in Pl/Python, which makes developers to convert string into number data type in plptyhon and do some processing on it, which is kind of extra coding. However in 9.3 they don't have to worry any more. Below is an example to test this feature.

CREATE FUNCTION test_type_conversion_oid(x oid) RETURNS oid AS $$
   plpy.info(x, type(x))
   return x +1
$$ LANGUAGE plpythonu;

Lets look at the pre 9.3

worktest=# select test_type_conversion_oid(123456);
INFO:  ('123456', )
CONTEXT:  PL/Python function "test_type_conversion_oid"
ERROR:  TypeError: cannot concatenate 'str' and 'int' objects
CONTEXT:  Traceback (most recent call last):
  PL/Python function "test_type_conversion_oid", line 3, in 
    return x +1
PL/Python function "test_type_conversion_oid"

which shows direct number operations is not possible with OID in pre9.3, since OID in pre 9.3 is treated as string.

In 9.3

postgres=# select test_type_conversion_oid(123456)
postgres-# ;
INFO:  (123456L, )
CONTEXT:  PL/Python function "test_type_conversion_oid"
 test_type_conversion_oid 
--------------------------
                   123457
(1 row)

This works great and direct number operation with OID is possible. A good addition in plpython.

iii. Handle SPI errors raised explicitly (with PL/Python's RAISE) the same as internal SPI errors

This is new addition to plpython. Now in plpython function's body, user can raise SPIError/exceptions using "raise" statement of plpython, which was missing in pre 9.3. Now in 9.3, user will be able to use "raise" statement to raise exceptions. Addition to this, if user sets the sqlstate attribute, plpython preserver that change. Below is an example:
Following is definition of function which can be use for testing this addition in pre 9.3 and in 9.3

CREATE OR REPLACE FUNCTION plpy_raise_spiexception() RETURNS void AS $$
   raise plpy.spiexceptions.DivisionByZero()
$$ LANGUAGE plpythonu;

Lets use anonymous block of plpgsql to test how raise exception can be handle.

Before 9.3

DO $$
BEGIN
   SELECT plpy_raise_spiexception();
EXCEPTION WHEN division_by_zero THEN
   RAISE NOTICE 'Found Exception';
END
$$ LANGUAGE plpgsql;

ERROR:  spiexceptions.DivisionByZero: 
CONTEXT:  Traceback (most recent call last):
  PL/Python function "plpy_raise_spiexception", line 2, in 
    raise plpy.spiexceptions.DivisionByZero()
PL/Python function "plpy_raise_spiexception"
SQL statement "SELECT plpy_raise_spiexception()"
PL/pgSQL function inline_code_block line 3 at SQL statement

As you can see Before 9.3, user will raise statement was not working properly for raising exception in plpython.

In 9.3

postgres=# DO $$
postgres$# BEGIN
postgres$#    SELECT plpy_raise_spiexception();
postgres$# EXCEPTION WHEN division_by_zero THEN
postgres$#    RAISE NOTICE 'Found Exception';
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE:  Found Exception
DO

As you can see in 9.3. it works great!