GET DIAGNOSTICS with COPY statement in Pl/pgsql function

Posted on by Raghav
Now GET DIAGNOSTIC will return the number of rows processed by COPY statement in a Pl/Pgsql function.
COPY statement in Pl/Pgsql Function:

CREATE OR REPLACE FUNCTION public.copy_data(fname text) RETURNS integer
AS 
$$
declare
    copy_qry text;
    cnt integer;
Begin
copy_qry := 'copy t from'||quote_literal(fname)||' with CSV HEADER;';
Execute copy_qry;
GET DIAGNOSTICS cnt = ROW_COUNT;
return cnt;
end;
$$ Language plpgsql;

Previous release:

-bash-4.1$ psql
psql.bin (9.2.3)
Type "help" for help.

postgres=# select copy_data('/usr/local/pg93beta/t_load.csv');
 copy_data
-----------
         0
(1 row)

In PostgreSQL 9.3

-bash-4.1$ ./psql -p 5555
psql (9.3beta1)
Type "help" for help.

postgres=# select copy_data('/usr/local/pg93beta/t_load.csv');
 copy_data
-----------
         3
(1 row)
or
-bash-4.1$ ./psql -p 5555 -c "select copy_data('/usr/local/pg93beta/t_load.csv');"
 copy_data
-----------
         3
(1 row)

Thanks to author, it looks simple but very effective when working with loading the data using scripts and want to know the number of rows processed by the COPY statement.

–Raghav