How to set Postgres global variable on session from spring data jpa context

Question

Basically I want to execute this query
set public.ownerId = 'Owner-3';

I have tried this in JPA, but no luck. Getting unexpected token set error .

 @Modifying
 @Query(value = "set public.ownerId = :ownerId;")
 public void setGlobalVariableOwnerId(String ownerId);

Error:
2021-03-31 10:19:07.243 ERROR 1 --- [      main] o.h.hql.internal.ast.ErrorTracker    : line 1:1: unexpected token: set
2021-03-31 10:19:07.245 ERROR 1 --- [      main] o.h.hql.internal.ast.ErrorTracker    : line 1:1: unexpected token: set
antlr.NoViableAltException: unexpected token: set
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:212) ~[hibernate-core-5.4.27.Final.jar!/:5.4.27.Final]

If I hardcode the value instead of parameter like this set public.ownerId = 'Owner-3' .It works.

Please help Thanks in advance

What happends if you change to @Query(value = "set public.ownerId = ?1") — Kaj Hejer
– Kaj Hejer, Commented Mar 31, 2021 at 16:35
engine.jdbc.spi.SqlExceptionHelper : ERROR: syntax error at or near "$1" org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1". @Query(value = "set public.ownerId = ?1", nativeQuery = true) — Sushma Kundapur
– Sushma Kundapur, Commented Apr 6, 2021 at 5:18

Youssouf Maiga · Accepted Answer · 2022-01-14 16:50:58Z

2

For preparedstatements, you have to use, you have to use set_config method of Postgre.

Your query should be something like :

 @Query(value = "SELECT set_config('public.ownerId',:ownerId, false)")

The query will return the value you set.
The third paramater is about the scope: false for session and true for transaction.

answered Jan 14, 2022 at 16:50

Youssouf Maiga

7,96110 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

How to set Postgres global variable on session from spring data jpa context

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