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....

Xever

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)

Gage

Hey - Thanks, you solved the first problem. Stupid mistake on my part! However, problem 2 still exists. There can be multiple rows in the table with the same GaugeID - I want the max, and min dates from those. If I run the query outside of a function, it works perfectly. Inside the function, it gets the max and min across every row in the table. – Xever

could you give a sample of your dataset for the relative rows? There is nothing special about running the query in the function. The query processor runs the FROM and WHERE portions of a query first so it has to be restricting the results based on the where clause. Are you sure your function is updated? /df+ function_name. – Gage

(Updated the main question) I've checked that the function is loaded by running CREATE OR REPLACE FUNCTION.... – Xever

ok sorry but I have to ask: have you looked through the results of SELECT * FROM GaugeSummary WHERE GaugeID = 4491? hehe :) As a side note, you are not using the alias and you can reference the parameter directly. – Gage

Hey - yes that's what I'm comparing my function call to. The SELECT * gives me back 2 rows - and looking at the values, I can see what my true max and min should be. Running the SELECT min, max... outside of the function gives me the correct results. Running inside the function fails.... Thanks for the tip re. the parameter - it got left there from a lot of mucking around :) – Xever

Hmm - I just rewrote the function using LANGUAGE 'SQL' STABLE; and it works perfectly. So it must be something I'm doing wrong specific to plpgsql... – Xever

Ok - I took your function, changed the names, and I still get the same error. I'm running this using 8.4, and I'm using the pgadmin III tool that comes with PostgreSQL. – Xever

hmm, STABLE shouldn't be affecting it. I just am not sure what is going on. I put all the test conditions in and it worked fine for me. Glad you got it working though. – Gage

oh, is pgadmin commiting after you 'create or replace function...'? what is the current function definition for your function :) I am running out of ideas hehe. – Gage

I'm at a loss too... Running \df+ gives me back what I expected - precisely the same contents of the plpgsql function that I executed in pgadmin. So that is the function that's being run. – Xever

Furthermore, here is the output from calling the SQL from the command line (removing pgadmin as a possible source of confusion: (with plpsql function) # SELECT * FROM GaugeSummary_GetDateRangeForGauge(4491); minimum | maximum ---------------------+--------------------- 1753-01-01 12:00:00 | 2009-11-11 03:45:00 (1 row) (with SQL STABLE function) # SELECT * FROM GaugeSummary_GetDateRangeForGauge(4491); minimum | maximum ---------------------+--------------------- 2009-03-28 12:00:00 | 2009-06-27 12:00:00 (1 row) – Xever