<sql-insert callable="true">{call mydb.createMyEntity (?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>

CREATE OR REPLACE FUNCTION mydb.createMyEntity(
  a_name character varying(50),
  a_address character varying(200),
  a_city character varying(100),
  a_post_code character varying(10),
  a_country_code character varying(3),
  a_foo character varying(200),
  a_bar_fk_id integer,
  a_my_id integer
)   RETURNS void AS $$
BEGIN

INSERT INTO mydb.my_table VALUES ($8,  $7,  $1 ,  $2 ,  $3,  $4 ,  $5,  $6 );

END;
$$ LANGUAGE plpgsql;

Hibernate: {call mydb.createMyEntity (?, ?, ?, ?, ?, ?, ?, ?)}
18:35:05.343 [main] ERROR org.hibernate.jdbc.AbstractBatcher - Exception executing batch:
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

<class> ... (normal definition of id, properties and relations ...)

<loader query-ref="loadShopSP"/> <!-- see below for definition -->

         <!-- add check="none" as workaround for failure
            see http://opensource.atlassian.com/projects/hibernate/browse/HHH-1792 -->
      <sql-insert  check="none" callable="true">{call createShopHB (?, ?, ?, ?, ?, ?, ?)}</sql-insert>
      <sql-update check="none" callable="true">{call updateShopHB(?, ?, ?, ?, ?, ?, ?)}</sql-update>
      <sql-delete check="none" callable="true">{call deleteShopHB(?)}</sql-delete>
   </class>

<!-- load Shop by custom SQL instead of automatic hibernate generated code -->
<!-- this is not a stored procedure, but I left it here ... -->
<sql-query name="loadShop">
    <return alias="shop" class="entity.Shop" lock-mode="upgrade"/>
    SELECT
      NAME AS {shop.name},
      ID AS {shop.id},
      address AS {shop.address},
      city AS {shop.city},
      post_code AS {shop.postCode},
      country_code AS {shop.countryCode},
      working_hours AS {shop.workingHours}
    FROM shop
    WHERE ID=?
    FOR UPDATE
</sql-query>

<!-- try loading Shop by using a stored procedure -->
   <sql-query name="loadShopSP" > <!-- callable="true"  does not work   -->
      <return alias="shop" class="entity.Shop">
         <return-property name="employee" column="EMPLOYEE" />
         <return-property name="id" column="id" />
         <return-property name="name" column="name" />
         <return-property name="address" column="address" />
         <return-property name="city" column="city" />
         <return-property name="postCode" column="post_code" />
         <return-property name="countryCode" column="country_code" />
         <return-property name="workingHours" column="working_hours" />
      </return>
      <!--{? = call loadShopHB(?)} this way does not work -->
      select * from loadShopHB(?)
   </sql-query>

<!-- custom query that returns a list of Shop objects  -->
   <sql-query name="selectAllShops" > <!-- callable="true"  does not work   -->
      <return alias="shop" class="entity.Shop">
         <return-property name="id" column="id" />
         <return-property name="name" column="name" />
         <return-property name="address" column="address" />
         <return-property name="city" column="city" />
         <return-property name="postCode" column="post_code" />
         <return-property name="countryCode" column="country_code" />
         <return-property name="workingHours" column="working_hours" />
      </return>
      <!-- {? = call getAllShopsHB()}  this way does not work -->
      select * from getAllShopsHB()
   </sql-query>

CREATE OR REPLACE FUNCTION createShopHB(
  a_name character varying(50),
  a_address character varying(200),
  a_city character varying(100),
  a_post_code character varying(10),
  a_country_code character varying(3),
  a_working_hours character varying(200),
  a_id integer
) RETURNS void  AS $$

BEGIN

INSERT INTO shop VALUES (
  a_id,
  a_name ,
  a_address ,
  a_city ,
  a_post_code ,
  a_country_code ,
  a_working_hours )
;

END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION updateShopHB(
  a_name character varying(50),
  a_address character varying(200),
  a_city character varying(100),
  a_post_code character varying(10),
  a_country_code character varying(3),
  a_working_hours character varying(200),
  a_id integer
) RETURNS void  AS $$

BEGIN

  update shop set
    name=a_name, address=a_address, city=a_city, post_code=a_post_code,
    country_code=a_country_code, working_hours=a_working_hours
  where id=a_id;

END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION deleteShopHB(
  a_id integer
) RETURNS void  AS $$

BEGIN

  delete from shop where id=a_id;

END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION loadShopHB(
  a_id integer
) RETURNS SETOF shop  AS $$

BEGIN
RETURN QUERY SELECT *
    FROM shop
    WHERE
      id = a_id;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION getAllShopsHB()
  RETURNS SETOF shop  AS $$

BEGIN
RETURN QUERY SELECT *
    FROM shop;
END;
$$ LANGUAGE plpgsql;

Boolean result = (Boolean) session.
    createSQLQuery("select hasUserRight(:param1, :param2)").
    setParameter("param1", userId).
    setParameter("param2", rightId).
    uniqueResult();

////////////

Store result = (Store) session
    .createSQLQuery("select* from loadStoreHB(:param1)").
    addEntity(Store.class).
    setParameter("param1", id).
    uniqueResult();

////////

List<Store> result = (List<Store>) session.
    createSQLQuery("select * from getAllStoresHB()").
    addEntity(Store.class).
    list();

CREATE OR REPLACE FUNCTION hasUserRight(a_user_id int, a_right_id int) RETURNS boolean
AS $$
DECLARE
    quantity integer;
BEGIN

SELECT count(*) INTO quantity
FROM xxxxxx //details deleted//
WHERE
  user_id = a_user_id
   AND  right_id = a_right_id
;

RETURN quantity > 0;

END;
$$ LANGUAGE plpgsql