Thread: Call sql function in psql

> On Jan 18, 2018, at 9:22 AM, Abhra Kar <abhra.kar@gmail.com> wrote:
>
> Hi All,
>
>               Please send me some info how to post directly in mailing list,somwhow I am not able to find out the way
soI am mailing here. 
>
>
Here is fine

>
> Present problem--
>
>
>
>               I tried to change below code in psql —
>
>
> Session sess = (Session) entityManager.getDelegate();
>
>  sess.createSQLQuery("{ call reset_all() }").executeUpdate();
>
>
> Here 'sess' is  org.hibernate.Session and reset_all() is a function which contains—
>
>
> DECLARE
>
> username varchar(30);
>
> BEGIN
>
> select user into username;
>
> if username like 'XXXXX%' then
>
> update YYYYY set aaaa = 0;
>
> update ZZZZ set bbbb= 0;
>
> EXECUTE('truncate table abc');
>
> EXECUTE('truncate table def');
>
> ::::::::::::::::::::::::::::::::::::::::::::::::
>
> else
>
> RAISE NOTICE 'User not allowed to run this procedure';
>
> end if;
>
> end;
>
>
> I tried to modify like
>
>
>         sess.createSQLQuery("select reset_all()").executeUpdate();
>
>
> getting error -- org.postgresql.util.PSQLException: A result was returned when none was expected.
>
>
> What should be the syntax of calling reset_all().
>
>
>
> Thanks and Regards,
>
> Abhra
>
>
>
At the very least change ‘call’ to ‘select’.  Are those braces (“{}”) truly necessary? (Been a while since I’ve user
hibernate).