Returning a Custom Type from a Postgresql function

Question

I'm 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);

---

There are two problems I'm having with this.

1) - My results come back as a single column as "("1753-01-01 12:00:00","2009-11-11 03:45:00")", where I need them to come back in two columns.

Solved! - Silly mistake... It should be SELECT * FROM GaugeSummary_GetDateRangeForGauge(123)

2) The results are the maximum and minimum values from the whole table - the WHERE constraint isn't being 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 a call to the function gives me : "1753-01-01 12:00:00", "2009-11-11 03:45:00"

Thanks!

Answer for 2:

It seems that running this same query inside a "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 isn't working correctly....

Answer 1

I tried this and I get two columns back when doing

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 are getting your results?

As for #2, if you just want the results then remove the min() and max() aggregate functions from your query. Removing those will ensure that the results from those columns will be returned on the row that matches your ID.

UPDATE: ok I am not sure whats going on then. I just put all the similar stuff into my test DB and its working as I expect it to.

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)