how to use the parameter value for dynamically built SQL queries inside postgresql function?

Question

I'm trying to compile a postgresql function

CREATE OR REPLACE FUNCTION LoadData(tablename varchar(25), filepath varchar(35))
RETURNS void AS $$
declare

BEGIN

RAISE NOTICE 'Data is being loaded from an external file, please wait...';

COPY tablename FROM filepath DELIMITER ',' CSV HEADER;


RAISE NOTICE 'Data loaded successfully!!';
END;
$$ LANGUAGE plpgsql;

but it's giving an error like this

ERROR:  syntax error at or near "filepath"
LINE 9: COPY tablename FROM filepath DELIMITER ',' CSV HEADER;

How to use both the in parameters in the COPY command?

Clodoaldo Neto · Accepted Answer · 2014-08-06 12:18:37Z

1

To execute dynamically built SQL queries use execute. format makes it easy and safe

create or replace function loaddata(
    tablename varchar(25), filepath varchar(35)
) returns void as $$
begin

raise notice 'data is being loaded from an external file, please wait...';

execute format($copy$
    copy %I from %L delimiter ',' csv header
    $copy$,
    tablename, filepath
);

raise notice 'data loaded successfully!!';
end;
$$ language plpgsql;

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

edited Aug 6, 2014 at 12:18

answered Aug 6, 2014 at 12:06

Clodoaldo Neto

127k30 gold badges250 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use the parameter value for dynamically built SQL queries inside postgresql function?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related