Using parameters passed to stored procedure in query

Question

I have a sql query where I want to extract records older than 'X' number of days, here for eg its 7 days:

SELECT * FROM BOOKMARK.MONITORING_TABLE WHERE inserteddatetime < (now() - '7 day'::interval);

I have to execute this query through a stored procedure passing in the configurable 'X' no of days as arguments. The procedure is as below:

CREATE OR REPLACE FUNCTION DELETE_REDUNDANT_RECORDS_STORED_PROCEDURE(days int)
RETURNS void AS
$func$
DECLARE
    rec_old                  RECORD;
    cursor_data CURSOR FOR
        SELECT * FROM BOOKMARK.MONITORING_TABLE WHERE inserteddatetime < now() - '$1 day'::interval;

BEGIN
    OPEN cursor_data;
    // business logic for the procedure
    CLOSE cursor_data;
END;
$func$
LANGUAGE plpgsql;

This doesn't work as I am not able to use the placeholder for days in my query. How do we use the arguments passed to my query in this case.

Unrelated, but: why the loop what exactly are you doing inside that loop? A loop (cursor or not) is very often not the right tool - a set based approach is usually a much more efficient solution. But without seeing the real code in your loop this is hard to tell. — user330315
– user330315, Commented Aug 1, 2019 at 9:13

user330315 · Accepted Answer · 2019-08-01 09:10:11Z

1

To create an interval based on an integer, make_interval() is much easier to use than casting to an interval type.

Additional I wouldn't use a cursor, but a FOR loop based on a SELECT statement (maybe using make_interval(days => $1) works in the cursor declaration as well)

CREATE OR REPLACE FUNCTION DELETE_REDUNDANT_RECORDS_STORED_PROCEDURE(days int)
  RETURNS void AS
$func$
DECLARE
  rec_old record;
BEGIN
    for rec_old in SELECT * 
                   FROM BOOKMARK.MONITORING_TABLE 
                   WHERE inserteddatetime < now() - make_interval(days => $1)
    loop
        raise notice 'records %', rec_old;
    end loop;
END;
$func$
LANGUAGE plpgsql;

answered Aug 1, 2019 at 9:10

user330315

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

1 Comment

Abhilash Over a year ago

I haven't added the entire Stored procedure logic intentionally, but this method solves my need

Collectives™ on Stack Overflow

Using parameters passed to stored procedure in query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related