I want to do copy csv to database using stored procedure. My function is follows;
CREATE FUNCTION gis_portal.copycsv(IN path text) RETURNS void AS
'COPY gis_portal.temp_excel FROM path WITH DELIMITER'
LANGUAGE sql VOLATILE LEAKPROOF;
query is
COPY gis_portal.temp_excel FROM path WITH DELIMITER ',' CSV HEADER
with parameter path.
It is giving error as syntax error near path while creating function.
Please help me.
'COPY gis_portal.temp_excel FROM path WITH DELIMITER'
^^^^^^^^^^
What delimiter? You must specify one if you use that keyword.
Try:
CREATE FUNCTION gis_portal.copycsv(IN path text) RETURNS void AS $$
COPY gis_portal.temp_excel FROM path WITH DELIMITER ','
$$ LANGUAGE sql;
or whatever delimiter you want.
Additionally, in SQL functions you can't use the identifier you must use a positional parameter, like $1. But because COPY isn't a plannable statement you cannot use parameters in it. You will have to use PL/PgSQL and EXECUTE to run it as dynamic SQL:
CREATE FUNCTION gis_portal.copycsv(IN path text) RETURNS void AS $$
BEGIN
EXECUTE format('COPY gis_portal.temp_excel FROM %L WITH DELIMITER '',''', path);
END;
$$ LANGUAGE plpgsql;
Note the doubled quotes around the delimiter because it's now an SQL string.
