Returning a custom type from a Postgresql function

Question

I am trying to return a custom type from a PostgreSQL function as follows:

---

DROP TYPE IF EXISTS GaugeSummary_GetDateRangeForGauge_Type CASCADE; -- Drop our previous type CREATE TYPE GaugeSummary_GetDateRangeForGauge_Type AS -- Recreate our type ( Minimum timestamp without time zone, Maximum timestamp without time zone ); CREATE OR REPLACE FUNCTION GaugeSummary_GetDateRangeForGauge ( GaugeID integer ) RETURNS GaugeSummary_GetDateRangeForGauge_Type AS $$ DECLARE iGaugeID ALIAS FOR $1; oResult GaugeSummary_GetDateRangeForGauge_Type%ROWTYPE; BEGIN SELECT INTO oResult min(ArchivedMinimumTime) as Minimum, max(TelemeteredMaximumTime) as Maximum FROM GaugeSummary WHERE GaugeID = $1; RETURN oResult; END; $$ LANGUAGE plpgsql; SELECT GaugeSummary_GetDateRangeForGauge(2291308); 

---

I have two problems with this.

one). My results are returned as a single column like "(" 1753-01-01 12:00:00 "," 2009-11-11 03:45:00 "), where I need them, return to the two columns.

Solved! - Stupid mistake ... It must be SELECT * FROM GaugeSummary_GetDateRangeForGauge (123)

2) The results are the maximum and minimum values ​​from the entire table - the WHERE constraint is not used.

Example:

 GaugeSummaryID GaugeID ArchivedMinimumTime TelemeteredMaximumTime 80 4491 "2009-03-28 12:00:00" "2009-06-27 12:00:00" 81 4491 "2009-03-28 12:00:00" "2009-06-27 12:00:00" 

But calling the function gives me: "1753-01-01 12:00:00", "2009-11-11 03:45:00"

Thanks!

Answer for 2:

This same query seems to be running inside the "LANGUAGE" SQL "STABLE"; function works fine:

 CREATE OR REPLACE FUNCTION GaugeSummary_GetDateRangeForGauge ( GaugeID integer ) RETURNS GaugeSummary_GetDateRangeForGauge_Type AS $$ SELECT min(ArchivedMinimumTime) as Minimum, max(TelemeteredMaximumTime) as Maximum FROM GaugeSummary WHERE GaugeID = $1; $$ LANGUAGE 'SQL' STABLE; 

However, it would be nice to know why the plpgsql function does not work correctly ....

Answer 1

I tried this and I return two columns when executed

 SELECT * GaugeSummary_GetDateRangeForGauge(1); 

results:

 aadb=# select * from GaugeSummary_GetDateRangeForGauge(1); minimum | maximum ----------------------------+---------------------------- 2010-01-11 15:14:20.649786 | 2010-01-11 15:14:24.745783 (1 row) 

I am using 8.4 and running it in psql. Could you clarify how you get your results?

As for # 2, if you just want to get the results, remove the min () and max () aggregation functions from your query. Removing these objects ensures that the results from these columns are returned in the row corresponding to your identifier.

UPDATE : ok I'm not sure what happens next. I just put all such things in my test database and its work, as I expect.

custom type

 create type custom_type as ( minimum timestamp without time zone, maximum timestamp without time zone); 

table (test)

 aadb=# select * from test order by id; id | a | b ----+----------------------------+---------------------------- 1 | 2010-01-11 17:09:52.329779 | 2010-01-11 17:09:52.329779 1 | 2010-01-11 17:10:04.729776 | 2010-01-11 17:10:04.729776 2 | 2010-01-11 17:09:55.049781 | 2010-01-11 17:10:21.753781 2 | 2010-01-11 17:10:30.501781 | 2010-01-11 17:10:30.501781 3 | 2010-01-11 17:09:58.289772 | 2010-01-11 17:09:58.289772 3 | 2010-01-11 17:35:38.089853 | 2010-01-11 17:35:38.089853 (6 rows) 

function

 create or replace function maxmin (pid integer) returns custom_type as $$ declare oResult custom_type%rowtype; begin select into oResult min(a) as minimum, max(b) as maximum from test where id = pid; return oResult; end; $$ language plpgsql; 

results

 aadb=# select * from maxmin(2); minimum | maximum ----------------------------+---------------------------- 2010-01-11 17:09:55.049781 | 2010-01-11 17:10:30.501781 (1 row)