Using copy command within format in a stored procedure using Postgres plpgsql

Question

I am trying to create a stored procedure in Postgres for generating CSV files containing the average values of some tags/variables between the last 15 minutes. But when I try to implement the 'where' condition for selecting the tag values in the last 15 minutes using Execute 'format()' method, it gives me syntax errors as output. Since the actual code with multiple tags is too complicated, I am having a hard time fixing the syntax of this code. Can you guys please take a look at this? I am doing this in Postgres SQL using PLPGSQL language. Here I am able to add my file name dynamically, but conditions in the select query are generating problems.

CREATE OR REPLACE PROCEDURE public.csv_test0002()

LANGUAGE plpgsql

AS $$ 

    declare sDate varchar;
    declare sFileName  varchar;
    declare DEST varchar;
    DECLARE sFromTime varchar(150);
    DECLARE sToTime varchar(150);
begin
    sDate := to_char(current_timestamp, 'YYYY-MM-DD_HH24_MI_SS');
    DEST := concat('Filename_',sDate);
    sFromTime := TO_CHAR(current_timestamp - interval '15 minutes', 'YYYY-MM-DD HH24:MI:SS');
    sToTime := TO_CHAR(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');
EXECUTE format(
    'COPY'
    '(select avg(ptvalue) as ptvalue from pro_opc where ptname = ''OPC_SR'' and pttime between ',sFromTime,' and ',sToTime,')'
    'to %L CSV HEADER',
    'D:\' || DEST || '.csv'
     );
end $$;

The problematic part is the sFromTime and sTotime in the select query. Postgres is not recognizing the value inside them. Can you help me correct the syntax of the format() function here so that I can pass the timestamp condition dynamically?!! Output image of current code when the stored procedure is called

Shouldn't you concatenate them? They're varchar and only need a quote — edd, Aug 07 '21 at 18:59
Unrelated, but: `declare` starts a _block_ where you can define multiple (all) variables. There is no need to start a new `declare` block for each variable. — , Aug 07 '21 at 19:54
I have declared the copy command into separate strings and executed them together using the 'execute' command within stored procedure without format and now it works dynamically. My filename and time condition is now dynamic. — Joyal Joy, Aug 08 '21 at 15:51

Stefanov.sm · Answer 1 · 2021-08-08T19:22:32.893

1

Try this using double-dollar quoting format for better readability.
Keeping your query -

execute format
(
 $$
  COPY 
  (
    select avg(ptvalue) as ptvalue,
    from pro_opc 
    where ptname = 'OPC_SR' 
    and pttime between %L and %L
  )
  to %L CSV HEADER
 $$,
 sFromTime,
 sToTime, 
 'D:\' || DEST || '.csv'
);

But better remove sFromTime and sToTime completely and use this equivalent query.

execute format
(
 $$
  COPY 
  (
    select avg(ptvalue) as ptvalue,
    current_timestamp as local_time 
    from pro_opc 
    where ptname = 'OPC_SR' 
    and pttime between current_timestamp - interval '15 minutes' 
                   and current_timestamp
  )
  to %L CSV HEADER
 $$,
 'D:\' || DEST || '.csv'
);

edited Aug 08 '21 at 19:22

answered Aug 07 '21 at 20:05

Stefanov.sm

11,215
2
21
21

Thank you, I have another query though, How can we add local time as a column in the same CSV file? – Joyal Joy Aug 08 '21 at 15:58
Just add `current_timestamp as local_time` to the select list. I have updated the second query accordingly. Does this solve your issue? – Stefanov.sm Aug 08 '21 at 19:24
Really appreciate your support. Since I am using multiple combinations of select queries to display data from one table into a result table, I want a column of the date_time associated with the data. There will be a difference in the number of returning rows if add the date_ time like this. But I figured out another way using aggregate functions. – Joyal Joy Aug 29 '21 at 14:35

Using copy command within format in a stored procedure using Postgres plpgsql

1 Answers1