SETOF Record Problem
By referring to http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS, section 34.4.8. SQL Functions 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!
In response to Yan Cheng Cheok :
By referring to http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS, section 34.4.8. SQL Functions Returning TABLE
That's for language SQL, you are using:
CREATE OR REPLACE FUNCTION get_measurements(IN bigint)
LANGUAGE 'plpgsql' VOLATILE
language plpgsql. That's not the same ...
But the example doesn't use any "INTO" or "RETURN".
Sure? Read again:
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html
Rewrite your to RETURN <insert your select here>;
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99