postgresql: pass variable to sql query

Question

I'm trying to pass a variable to a select statement using PostgreSQL. The overall goal is to use this logic in a stored procedure where the variable deadline_interval would be an input parameter. Here's what I've tried so far:

DO
$$
DECLARE
  date_deadline date;
  deadline_interval varchar := '6 month';
BEGIN
  date_deadline := (SELECT CURRENT_DATE - INTERVAL deadline_interval);
  RAISE NOTICE 'here: %', date_deadline;
END;
$$

Unfortunately the variable deadline_interval is undefined in the select statement. What am I doing wrong here?

sticky bit · Accepted Answer · 2018-08-24 00:12:36Z

1

Use a cast instead of the prefix.

...
date_deadline := (SELECT CURRENT_DATE - deadline_interval::interval);
...

Or, even better, use interval as type for deadline_interval from the beginning.

DO
$$
DECLARE
  date_deadline date;
  deadline_interval interval := INTERVAL '6 month';
BEGIN
  date_deadline := (SELECT CURRENT_DATE - deadline_interval);
  RAISE NOTICE 'here: %', date_deadline;
END;
$$

edited Aug 24, 2018 at 0:12

answered Aug 24, 2018 at 0:05

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

postgresql: pass variable to sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related