CODEWITHSUNDEEP
postgresql
Christiaan Westerbeek
Christiaan Westerbeek

Reputation: 11137

How to return a custom type from a function using language sql without declaring the type in the function?

Just reading this answer about using a new type to return multiple fields in PostgreSQL.

So we can do this.

CREATE TYPE my_type AS (a text, b text, c text)

CREATE OR REPLACE FUNCTION get() 
RETURNS my_type AS 
$$
DECLARE
  result_record my_type;

BEGIN
  SELECT r[1], r[2], r[3]
  INTO result_record.a, result_record.b, result_record.c
  FROM regexp_split_to_array('a.b.c', '\.') r;

  RETURN result_record;
END
$$ LANGUAGE plpgsql;

I was wondering if we can do the same thing without declaring the custom type in the function and/or using the language sql instead of plpgsql?

Upvotes: 0

Views: 400

Answers (2)

Vicctor
Vicctor

Reputation: 823

Or using just SQL

select cast((r[1], r[2], r[3]) as my_type) from regexp_split_to_array('a.b.c', '\.') r;

Upvotes: 0

Christiaan Westerbeek
Christiaan Westerbeek

Reputation: 11137

Ok, so this question probably shouldn't have been asked, because the answer is almost intuitive.

CREATE OR REPLACE FUNCTION get2() 
RETURNS my_type AS 
$$
  SELECT r[1] AS a, r[2] AS b, r[3] AS c 
  FROM regexp_split_to_array('a.b.c', '\.') r;
$$ LANGUAGE sql;

Upvotes: 1

Related Questions