'Liquibase & Spring Boot - Create function that reads from file - Spring does not see the file
I have a simple .csv file that has 2 columns: username, value.
I want to create a function that reads the file, checks if user exists by username and executes a single insert command if it does.
I have created a function, but it seems Spring Boot does not see the file :(
Any help? Or maybe there's a better way to do that? I can't execute multiple inserts with nested select since there's a constraint that the userId field in table descriptions cannot be null.
Here's the code:
<changeSet id="333 - Setup user descriptions" author="me">
<sql>
CREATE OR REPLACE FUNCTION import_user_description()
RETURNS void
LANGUAGE plpgsql AS
'
DECLARE
userId integer;
BEGIN
create temp table temp (
username character varying,
value character varying
);
execute format('copy temp from %L with delimiter '','' quote ''"'' csv ', ./imports/users/descriptions.csv);
FOR name IN (select username from temp)
LOOP
if exists (select id from users where username = name)
select id into userId from users where username = name;
INSERT INTO descriptions (userId, description) VALUES (userId, description);
end if;
END LOOP;
drop table temp;
END';
select import_user_description();
</sql>
</changeSet>
I have also tried
COPY temp FROM './imports/users/descriptions.csv' WITH (FORMAT csv); but keep getting an error...
ERROR: syntax error at or near "." Position: 350
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
