SETOF Record Problem

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!

Responses

Browse pgsql-general by date

  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