I have a problem mapping a stored procedure returning a SETOF RECORDS.
This is the stored procedure
CREATE OR REPLACE FUNCTION plugin_statistic3.st_getoffline_period(
_devid integer,
_from timestamp without time zone,
_to timestamp without time zone)
RETURNS SETOF record
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
r RECORD;
diff_interval INTERVAL;
v_ctr INTEGER = 0;
v_online BOOLEAN;
v_start TIMESTAMP;
v_end TIMESTAMP;
v_computeFirst TIMESTAMP;
v_totaloffline INTERVAL = '0';
v_offlineData RECORD;
BEGIN
FOR r IN
SELECT * FROM plugin_statistic3.dblink('statisticserver', 'SELECT * FROM plugin_statistic3.statsdata_online
WHERE deviceid = ' || _devId ||
' AND pointintime BETWEEN ''' || _from || '''' || ' AND ''' || _to || '''
ORDER BY pointintime DESC') AS(pointintime TIMESTAMP, deviceid INTEGER, online BOOLEAN)
--WHERE deviceId = _devId
--AND pointintime BETWEEN _from AND _to
--ORDER BY pointintime DESC
LOOP
IF v_ctr = 0 THEN
v_online = r.online;
v_end = r.pointintime;
IF v_online = FALSE THEN
IF NOW() < _to THEN
v_computeFirst = NOW();
ELSE
v_computeFirst = _to;
END IF;
diff_interval = v_computeFirst - v_end;
v_offlineData := (r.pointintime, v_computeFirst, r.online, diff_interval);
RAISE NOTICE 'Record % ', v_offlineData; --v_offlineData,
RETURN NEXT v_offlineData;
END IF;
END IF;
RAISE NOTICE 'Pointintime % online %', r.pointintime, r.online;
IF v_online != r.online THEN
IF v_online = TRUE THEN
diff_interval = v_end - r.pointintime;
RAISE NOTICE 'Period offline % ', diff_interval;
v_totaloffline = v_totaloffline + diff_interval;
v_offlineData := (r.pointintime, v_end, r.online, diff_interval);
RAISE NOTICE 'Record % ', v_offlineData; --v_offlineData,
RETURN NEXT v_offlineData;
--(r.pointintime, r.online)::offlineData);
END IF;
v_end = r.pointintime;
v_online = r.online;
END IF;
v_ctr = v_ctr + 1;
END LOOP;
RAISE NOTICE 'Total period offline ============================> % ', v_totaloffline;
END;
$BODY$;
This is the mapped procedure
@NamedStoredProcedureQuery(
name = Config.getDeviceOfflinePeriod,
procedureName = STDBConfig.STSchema + ".st_getoffline_period",
parameters = {
@StoredProcedureParameter (type = Integer.class, mode = ParameterMode.IN, name = Config.IN_deviceId),
@StoredProcedureParameter (type = LocalDateTime.class, mode = ParameterMode.IN, name = Config.IN_fromPeriod),
@StoredProcedureParameter (type = LocalDateTime.class, mode = ParameterMode.IN, name = Config.IN_toPeriod),
@StoredProcedureParameter (type = Object[].class, mode = ParameterMode.OUT, name = Config.OUT_l)
})
Running in pgAdmin works
SELECT * from plugin_statistic3.st_getoffline_period(809, '2021-01-01 00:00', '2021-01-11 22:00') as (a timestamp, b timestamp, c boolean, d interval);
This is the code to execute it
StoredProcedureQuery procedureQuery = session
.createNamedStoredProcedureQuery(Config.getDeviceOfflinePeriod)
.setParameter(Config.IN_deviceId, deviceId)
.setParameter(Config.IN_fromPeriod, from)
.setParameter(Config.IN_toPeriod, to);
procedureQuery.getResultList();
I had several errors depending on the call; tried also with procedureQuery.execute() and mapping also as REF_CURSOR.
With REF_CURSOR I have this exception:
javax.persistence.PersistenceException: org.hibernate.HibernateException: PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered
With ParameterMode.OUT
engine.jdbc.spi.SqlExceptionHelper - ERROR: a column definition list is required for functions returning “record”
Any suggestion? Thanks in advance.