Change the parameter each time and run the sql script

user_5594712 0
Viewed 34

I am quite new to sql and have been trying to work on the following script to parameterize it.

This is my code:

select 
    dc.deviceid,
    dc.kernel_time,
    dc.crash_time,
    dc.crash_process,
    dps.start_time,
    dps.end_time,
    dps.start_kernel_time,
    dps.end_kernel_time,
    case
        when dc.kernel_time between dps.start_kernel_time and dps.end_kernel_time then 1
        when dc.crash_time between dps.start_time and dps.end_time then 2
        else 3
    end as flag,
    ROW_NUMBER () over (partition by dc.deviceid, dc.kernel_time, 
    dc.crash_time, dc.crash_process order by flag) row_num
from dummy.dummy_crashes dc
left outer join (select *
                 from dummy.dummy_power) as dps
           on dc.deviceid = dps.deviceid
           and ((dc.kernel_time between (dps.start_kernel_time + 10000) and (dps.end_kernel_time + 10000)) or (dc.crash_time between dps.start_time and dps.end_time))
order by dc.crash_time;

I need to test this script by changing the start_kernel_time and end_kernel_time with a certain int parameter value (in this example shown: 10000) every time. So, instead of modifying it in the code, I would like to create a function with the int parameter of choice and run this script. Would that be possible? I am really clueless as to how to achieve that.

My ideal idea would be something like this:

get_crashes(10000); <-- get records with adding int parameter (in start_kernel_time and end_kernel_time) as 10000

get_crashes(30000); <-- get records with adding int parameter as 30000 

get_crashes(80000); <-- get records with adding int parameter as 80000

I am really trying to understand how I could achieve this?

amazon-redshift parameters postgresql sql
1 Answers

I can't write a comment because i don't have 50rep, but here is my answer:

You can create a temp table with values that you want to pass and call cursor with simple query like:

SELECT [value] FROM *temptable*

After that, inside cursor just write script with single value from above temp table

UPDATE

DECLARE
cur CURSOR FOR select col1 from tempTable;
test_cur RECORD;
BEGIN
open cur;
LOOP
fetch cur into test_cur;
exit when test_cur = null;
if test_cur.col1 IS NOT NULL then
return next test_cur.col1;
end if;
END LOOP;
close cur;
END;

One note - I never write PostgreSQL, just have knowledge about SQL and find code on internet, so maybe you need to check documentation.

user_45949610
Related