CURRENT_LOAD_SOURCE

When called within the scope of a COPY statement, returns the file name used for the load. With an optional integer argument, it returns the Nth /-delimited path part.

If the function is called outside of the context of a COPY statement, it returns NULL.

If the current load uses a UDSource function that does not set the URI, CURRENT_LOAD_SOURCE returns the string UNKNOWN. You cannot call CURRENT_LOAD_SOURCE(INT) when using a UDSource.

Behavior type

Syntax

CURRENT_LOAD_SOURCE( [ position ])

Arguments

position (positive INTEGER)

Path element to return instead of returning the full path. Elements are separated by slashes (/) and the first element is position 1. If the value is greater than the number of elements, the function returns an error. You cannot use this argument with a UDSource function.

Examples

The following load statement populates a column with the name of the file the row was loaded from:

=> CREATE TABLE t (c1 integer, c2 varchar(50), c3 varchar(200));
CREATE TABLE

=> COPY t (c1, c2, c3 AS CURRENT_LOAD_SOURCE())
   FROM '/home/load_file_1' ON exampledb_node02,
        '/home/load_file_2' ON exampledb_node03 DELIMITER ',';
Rows Loaded
-------------
5
(1 row)

=> SELECT * FROM t;
c1  |      c2      |          c3
----+--------------+-----------------------
2   |  dogs        | /home/load_file_1
1   |  cats        | /home/load_file_1
4   |  superheroes | /home/load_file_2
3   |  birds       | /home/load_file_1
5   |  whales      | /home/load_file_2
(5 rows)

The following example reads year and month columns out of a path:

=> COPY reviews
        (review_id, stars,
         year AS CURRENT_LOAD_SOURCE(3)::INT,
         month AS CURRENT_LOAD_SOURCE(4)::INT)
FROM '/data/reviews/*/*/*.json' PARSER FJSONPARSER();