Postgresql Return a Function with a Custom Data Type

Returning a Custom Type from a Postgresql function

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)

PostgreSQL return a function with a Custom Data Type

This should just work. The enum should not be a problem. Tested with Postgres 9.1 and 9.2

CREATE TYPE building_code AS ENUM ('IT','EMS','HSB','ENG');
CREATE TEMP TABLE venue (id int PRIMARY KEY, building_code building_code);
INSERT INTO venue VALUES (1, 'ENG');

CREATE OR REPLACE FUNCTION room_code(_id int) --!
  RETURNS building_code AS 
$func$
SELECT building_code FROM venue v WHERE v.id = $1 -- !
$func$ LANGUAGE SQL;

SELECT * FROM room_code(1);

Except ...

• In versions before 9.2 you can only use positional (numeric) parameters ($1) in SQL functions (unlike plpgsql functions).
  
  In 9.2+ the column name would take precedence, so that the WHERE clause of your original code would always be TRUE and all rows would qualify - except that your function only returns the first, since it does not return a SETOF building_code.
  
  Either rename your parameter or use positional parameter or, preferably, both.
  
  If you must use conflicting parameter names, you can override the preference by using the function name to qualify the parameter. Like:

  ... WHERE v.id = room_code.id
  

• You shouldn't use the type name as column name.

• You should not use unquoted mixed case names like roomCode, which will be folded to lower case, unless you double-quote: "roomCode".

->SQLfiddle with 3 variants

Declare and return a custom type in PostgreSQL function

The syntax you are trying to use is foreign to Postgres.

Your code is much more complicated than it needs to be. Use a simple SQL function:

CREATE OR REPLACE FUNCTION get_details_for_widget(widgetid integer)
  RETURNS TABLE (id int, contact_id int, priority int, contact text)
$func$
   SELECT d.id, d.contact_id, d.priority, o.contact
   FROM   widget_details d
   JOIN   widget_owner   o ON o.id = d.contact_id
   WHERE  d.rid = widgetid   -- where does widgetid come from?
   AND    d.active_yn = 't'
   ORDER  BY d.priority
$func$ LANGUAGE sql

You don't need plpgsql at all for such a simple function. Use a plain SQL function instead.

Define an ad-hoc row type with RETURNS TABLE (). I improvised with the column types since you did not provide your table definitions. This works for plpgsql functions just as well.

Also:

• Use a proper JOIN condition for better readability.

• Simplify your query with table aliases.

• Use the data type boolean for widget_details.active_yn.

Boolean values

As clarified in the comment, it's a boolean column already. I would advice to use TRUE / FALSE instead of the string literals 't' / 'f' for data input - quoting the manual about the boolean type:

The key words TRUE and FALSE are the preferred (SQL-compliant) usage.

In a WHERE clause, every expression is evaluated to a boolean result. TRUE qualifies, FALSE or NULL do not. So, for a boolean type, you can simplify:

   AND    d.active_yn = TRUE

to just:

   AND    d.active_yn

Working with custom types in PostgreSQL and use them as arguments in functions

You are passing 5 parameters to the function, but it is declared to only accept one parameter (of type founder)

You need a row constructor to create a proper instance of founder and thus passing only a single argument:

SELECT companyTester(
             (76565445354,'Maks','Burkov','Maks.Burkov88@gmail.com','+_@Maks88')::founder
       );

The expression (76565445354,'Maks','Burkov','Maks.Burkov88@gmail.com','+_@Maks88')::founder is a single value

Alternatively you can use:

SELECT companyTester(row(76565445354,'Maks','Burkov','Maks.Burkov88@gmail.com','+_@Maks88'));

Postgres function returns custom data set

At a guess you're running:

SELECT extended_sales(1);

This will return a composite type column. If you want it expanded, you must instead run:

SELECT * FROM extended_sales(1);

Also, as @a_horse_with_no_name notes, a PL/pgSQL function is completely unnecessary here. Presumably this is a simplified example?

In future please include:

• Your PostgreSQL version; and
• The exact SQL you ran and the exact output you got

Use of custom return types in a FOR loop in plpgsql

my_test is a composite type, a row type, that contains a single integer field. It's not an integer.

When assigning to a record or row type in a FOR loop all output columns from the query form the row. Your SELECT needs to return a single, unnested integer column, which is then nested into a row type matching your custom type my_test.

You can also assign to a list of scalar variables in a FOR loop, in which case columns from the query are assigned left to right to variables as is - not forming a row.

If the column itself is a row type, you have one level of nesting to many. The text representation of a row containing an integer field is '(1)' (with parentheses!), and that's what you see in the error message.

You can fix that by extracting the integer field from the row with attribute notation:

SELECT (foo_out()).*

Or (more efficiently for multiple columns) by decomposing with:

SELECT * FROM foo_out()

Example Code

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test
  LANGUAGE sql AS
'SELECT ''(1)''::my_test';

CREATE FUNCTION foo1()
  RETURNS SETOF my_test
  LANGUAGE plpgsql AS
$func$
DECLARE
   x my_test;
BEGIN
   FOR x IN    
      SELECT * FROM foo_out()
   LOOP     
      RETURN NEXT x;
   END LOOP;
END
$func$;

db<>fiddle here

_{Old sqlfiddle}

Don't quote the language name plpgsql. It's an identifier.

Remember that looping is rarely needed, since most problems are more efficiently solved with a set-based approach (SQL only).

Related answers by Craig and Pavel:

• Passing array of a composite type to stored procedure

Return a table of custom type in Postgresql

You can use returns setof with a custom type:

create function foo() 
  returns setof mytype
as
...

---

Related Topics