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
– edd, Commented Aug 7, 2021 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. — user330315
– user330315, Commented Aug 7, 2021 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
– Joyal Joy, Commented Aug 8, 2021 at 15:51

Stefanov.sm · Accepted Answer · 2021-08-08 19:22:32Z

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 8, 2021 at 19:22

answered Aug 7, 2021 at 20:05

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

3 Comments

Joyal Joy Over a year ago

Thank you, I have another query though, How can we add local time as a column in the same CSV file?

Stefanov.sm Over a year ago

Just add current_timestamp as local_time to the select list. I have updated the second query accordingly. Does this solve your issue?

Joyal Joy Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related