Returning a Custom Type from a Postgresql function -

i'm trying return custom type postgresql function follows:

---

drop type if exists gaugesummary_getdaterangeforgauge_type cascade; -- drop our previous type  create type gaugesummary_getdaterangeforgauge_type   -- 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  $$ declare     igaugeid alias $1;     oresult     gaugesummary_getdaterangeforgauge_type%rowtype; begin     select oresult         min(archivedminimumtime) minimum,          max(telemeteredmaximumtime) maximum      gaugesummary      gaugeid = $1;      return oresult; end; $$ language plpgsql;  select gaugesummary_getdaterangeforgauge(2291308); 

---

there 2 problems i'm having this.

1) - results come single column "("1753-01-01 12:00:00","2009-11-11 03:45:00")", need them come in 2 columns.

solved! - silly mistake... should select * gaugesummary_getdaterangeforgauge(123)

2) results maximum , minimum values whole table - 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 call function gives me : "1753-01-01 12:00:00", "2009-11-11 03:45:00"

thanks!

answer 2:

it seems running same query inside "language 'sql' stable;" function works fine:

create or replace function gaugesummary_getdaterangeforgauge (     gaugeid integer ) returns gaugesummary_getdaterangeforgauge_type $$     select min(archivedminimumtime) minimum,          max(telemeteredmaximumtime) maximum      gaugesummary gaugeid = $1; $$ language 'sql' stable; 

however, nice know why plpgsql function isn't working correctly....

i tried , 2 columns when doing

select * gaugesummary_getdaterangeforgauge(1);   

results:

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

i using 8.4 , running in psql. clarify how getting results?

as #2, if want results remove min() , max() aggregate functions query. removing ensure results columns returned on row matches id.

update: ok not sure whats going on then. put similar stuff test db , working expect to.

custom type

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

table (test)

aadb=# select * test order id;  id |                          |             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 $$  declare     oresult custom_type%rowtype;  begin    select oresult min(a) minimum, max(b) maximum    test id = pid;     return oresult;  end;  $$ language plpgsql; 

results

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