By referring to http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS, section 34.4.8.
SQLFunctions Returning TABLE
I create the following stored procedures.
-- Function: get_measurements(bigint)
-- DROP FUNCTION get_measurements(bigint);
CREATE OR REPLACE FUNCTION get_measurements(IN bigint)
RETURNS TABLE(_type text, _value double precision, _unit text) AS
$BODY$DECLARE
_lotID ALIAS FOR $1;
BEGIN
SELECT measurement_type.value, measurement.value, measurement_unit.value
FROM unit, lot, measurement, measurement_unit, measurement_type
WHERE lot_id = fk_lot_id AND fk_unit_id = unit_id AND fk_measurement_unit_id = measurement_unit_id AND
fk_measurement_type_id = measurement_type_id AND
lot_id = _lotID;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION get_measurements(bigint) OWNER TO postgres;
However, whenever I call this function, using
SELECT * FROM get_measurements(1);
I get the following error :
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function "get_measurements" line 4 at SQL statement
********** Error **********
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "get_measurements" line 4 at SQL statement
But the example doesn't use any "INTO" or "RETURN".
Any hint?
Thanks!