Friday, May 31, 2013

GET DIAGNOSTICS with COPY statement in Pl/pgsql function

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

No comments :

Post a Comment