Обсуждение: SETOF Record Problem

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!

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

Thanks. I solved my problem using :

RETURN QUERY SELECT ...

Thanks and Regards
Yan Cheng CHEOK