Dynamic copy function

Question

I have a function with a dynamic copy statement and when I try to SELECT function, I get the error "query has no destination for result data"

select loadTodaysData();

CREATE FUNCTION loadTodaysData()
RETURNS VARCHAR(1000) AS $$
DECLARE vtoday VARCHAR(20) DEFAULT CURRENT_DATE;
BEGIN
  EXECUTE 'COPY nsn 
        FROM ''C:\Users\yuju\SQL_Data\test_''|| vtoday || ''.csv'' 
        with delimiter ','
        csv header';
END; $$
language plpgsql;

I want this to update my table everyday automatically from a folder. Function was created without an error.

A plain syntax problem. You didn't double all the single quotes in the statement string. — Laurenz Albe
– Laurenz Albe, Commented Jun 7, 2024 at 6:14

Lajos Arpad · Accepted Answer · 2024-06-06 16:25:24Z

0

Your stored function does not return a value, so you have nothing to select. Either return a value, like returning vtoday from your stored function and then you will have something to select, or, if you just want to execute your command, then refactor your function into a stored procedure and then use the CALL statement like

CALL loadTodaysData();

answered Jun 6, 2024 at 16:25

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

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

3 Comments

Yuju Takahashi Over a year ago

RETURN vtoday after the EXECUTE statement?

Lajos Arpad Over a year ago

@YujuTakahashi yes...

Yuju Takahashi Over a year ago

Thanks for the reply, I am still getting the same error message

Collectives™ on Stack Overflow

Dynamic copy function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related