1

I'm trying to use PostgreSQL IF sentence and I use MapSqlParametersSource to pass parameter to the SQL query. Interesting thing happens that if I pass a parameter to IF condition subquery it won't accept (interpret it properly) it, but if I define value in subquery then it will give me the results. So what I mean is this:

This works

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("recurr", true);
String sql = "DO " +
"$do$ " +
"BEGIN " +
 "IF (SELECT f.recurring_till FROM FINANCE_ENTITY f WHERE f.recurring = true) THEN SELECT amount, name FROM FINANCE_ENTITY; " +
"END IF; " +
"END " +
"$do$";
getNamedParameterJdbcTemplate().query(sql, params, BeanPropertyRowMapper.newInstance(FinanceEntity.class));

This return me results successfully.

This won't work

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("recurr", true);
String sql = "DO " +
"$do$ " +
"BEGIN " +
 "IF (SELECT f.recurring_till FROM FINANCE_ENTITY f WHERE f.recurring = :recurr) THEN SELECT amount, name FROM FINANCE_ENTITY; " +
"END IF; " +
"END " +
"$do$";
getNamedParameterJdbcTemplate().query(sql, params, BeanPropertyRowMapper.newInstance(FinanceEntity.class));

This will give me always following error:

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

My question is why I can't pass a parameter to my subquery using MapSqlParameterSource?

I use PostgreSQL 9.3.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Amir
  • 1,031
  • 2
  • 19
  • 42

1 Answers1

2

The problems with parameter passing aside (so this does not directly answer your question) you don't need either a DO statement or an IF for this. A simple SELECT does the job:

SELECT amount, name
FROM   FINANCE_ENTITY
WHERE  EXISTS (
   SELECT 1
   FROM   FINANCE_ENTITY
   WHERE  recurring = $1
   AND    recurring_till
   );

More importantly, you cannot return rows from a DO statement at all. So your claim "This return me results successfully" is ... a surprise to say the least. Because that's impossible:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for your answer. In fact when I use the one that works, I get an error where it says "More than one rows returned". Thus it actually return some rows. – Amir Jan 28 '16 at 08:49
  • @AmirAl: No, it does not. That's probably a PL/pgSQL error message (that obviously should be in your question to begin with) complaining that `IF` does not accept expressions returning more than one row. If you fix that, you'll get the next error complaining about the `SELECT` without target. I added some links with more explanation. – Erwin Brandstetter Jan 28 '16 at 08:55
  • Thanks for those links I will definitely check them. – Amir Jan 28 '16 at 09:01