From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SETOF Record Problem |
Date: | 2010-01-19 06:50:14 |
Message-ID: | 524292.28314.qm@web65716.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2010-01-19 07:18:46 | C: extending text search: from where to start |
Previous Message | Philippe Lang | 2010-01-19 06:40:00 | Index on immutable function call |