Как вернуть кастомный тип из sql функции?

Question

create type site_common_info as (
  engine    text,
  version   text,
  installed boolean
);


create function get_site_common_info()
  returns site_common_info
AS
$$
  SELECT ROW('MegaEngine', '1.0.2', false);
$$ language sql stable;

Error: return type mismatch in function declared to return site_common_info Подробности: Final statement returns record instead of text at column 1. Where: SQL function "get_site_common_info"

Answer 1

ROW в данном случае не нужно:

CREATE FUNCTION get_site_common_info()
RETURNS site_common_info
AS
$$
  SELECT 'MegaEngine', '1.0.2', FALSE;
$$
LANGUAGE SQL STABLE
;

Answer 2

Мы должны добавить явное приведение типа ::site_common_info

create type site_common_info as (
  engine    text,
  version   text,
  installed boolean
);


create function get_site_common_info()
  returns site_common_info
AS
$$
SELECT ('Mega', '1.0.2', false)::site_common_info;
$$ language sql stable;

БЛОГ НА HUSL

Как вернуть кастомный тип из sql функции?

Ответы (2 шт):