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 so I 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).

On Thu, Jan 18, 2018 at 9:22 AM, Abhra Kar <abhra.kar@gmail.com> wrote:

I tried to modify like

sess.createSQLQuery("select reset_all()")

​This part is correct​

.executeUpdate();

getting error -- org.postgresql.util.PSQLException: A result was returned
when none was expected.

​You need execute the above the same way you would execute "SELECT * FROM
tbl"; then just ignore the result set that is returned.

David J.
​

Thanks

On Thu, Jan 18, 2018 at 10:01 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote: