CODEWITHSUNDEEP
sqlpostgresql
Johnny Metz
Johnny Metz

Reputation: 6045

postgresql: pass variable to sql query

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?

Upvotes: 0

Views: 303

Answers (1)

sticky bit
sticky bit

Reputation: 37487

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;
$$

Upvotes: 1

Related Questions