Now GET DIAGNOSTIC will return the number of rows processed by COPY statement in a Pl/Pgsql function.
COPY statement in Pl/Pgsql Function:
--Raghav
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