PostgreSQL storing paths for reference in scripts

Question

I'm trying to find the appropriate place to store a system path in PostgreSQL.

What I'm trying to do is load values into a table using the COPY command. However, since I will be referring to the same file path regularly I want to store that path in one place. I've tried creating a function to return the appropriate path, but I get a syntax error when I call the function in the COPY command. I'm not sure if this is the right way to go about it, but I'll post my code anyway.

COPY command:

COPY employee_scheduler.countries (code, name)
    FROM get_csv_path('countries.csv')
    WITH CSV;

Function Definition:

CREATE OR REPLACE FUNCTION
    employee_scheduler.get_csv_path(IN file_name VARCHAR(50))
    RETURNS VARCHAR(250) AS $$
DECLARE
    path VARCHAR(200) := E'C:\\Brian\\Work\\employee_scheduler\\database\\csv\\';
    file_path VARCHAR(250) := '';
BEGIN
   file_path := path || file_name;
   RETURN file_path;
END;
$$ LANGUAGE plpgsql;

If anyone has a different idea on how to accomplish this I'm open to suggestions.

UPDATE:

The error I am receiving is:

ERROR: syntax error at or near "employee_scheduler" LINE 12: FROM employee_scheduler.get_csv_path('countries.csv')

I've tried the following statements with no luck:

COPY employee_scheduler.countries (code, name)
    FROM employee_scheduler.get_csv_path('countries.csv')
    WITH CSV;

COPY employee_scheduler.countries (code, name)
    FROM (employee_scheduler.get_csv_path('countries.csv'))
    WITH CSV;

COPY employee_scheduler.countries (code, name)
    FROM (SELECT * FROM employee_scheduler.get_csv_path('countries.csv'))
    WITH CSV;

I'm beginning to think the way I'm trying to achieve this isn't possible. Does anyone else have any ideas on how to achieve this? I might just have to hard code the path everywhere if I want to use the copy statement...

Thanks for any help.

Answer 1

What is the error?

Answer 2

Your function works fine. However, you need to say employee_scheduler.get_csv_path('countries.csv') instead of just get_csv_path('countries.csv') (unless I'm mistaken). Try this in psql and make sure it works:

=> SELECT employee_scheduler.get_csv_path('countries.csv');
                        get_csv_path                         
-------------------------------------------------------------
 C:\Brian\Work\employee_scheduler\database\csv\countries.csv
(1 row)

Answer 3

This not a right WAY. Calling Function in Copy Command is not going to help.

Create a function a as given below to make it workable: CREATE OR REPLACE FUnction copy_data(filename text) returns int as $$ declare cmd text; Begin cmd:='copy test_copy from '||''''||get_csv(filename)||''''||' with delimiter '||''''||','||''''||';'; execute cmd return 1; End; $$ language plpgsql;