Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Почему не удается вызвать функцию SETOF?  [new]
romaro
Member

Откуда:
Сообщений: 173
Запрашиваю из таблицы три текстовые колонки:
CREATE OR REPLACE FUNCTION public.setof_user_profile(
	p_json	json
)
	RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE
	l_user_key	TEXT	:= p_json ->> 'userKey';
BEGIN
	RETURN QUERY SELECT first_name, email, mobile_phone
	FROM public.USER
	WHERE id = l_user_key::numeric;

	RETURN;
END;
$function$


Получаю ошибку несоответствия типов:
автор
SQL Error [42804]: ERROR: structure of query does not match function result type
Detail: Returned type character varying(100) does not match expected type text in column 1.
Where: SQL statement "SELECT first_name, email, mobile_phone
FROM public.USER
WHERE id = l_user_key::numeric"
PL/pgSQL function setof_user_profile(json) line 5 at RETURN QUERY


На входе простой json:
SELECT public.setof_user_profile('{"userKey":"21"}');
29 окт 21, 04:04    [22389703]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5022
romaro,

написано - RETURNS SETOF text (т.е. 1 колонка типа text)
а вы 3 колонки возвращаете в запросе да ещё и 1е поле в ней не text типа.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
29 окт 21, 09:16    [22389731]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
О-О-О
Member

Откуда: Нижний Новгород
Сообщений: 363
romaro,



WHERE id = (p_json ->> 'l_user_key')::integer;

jsonb ->> text → text
Извлекает поле JSON-объекта по заданному ключу, в виде значения text.
'{"a":1,"b":2}'::json ->> 'b' → 2
29 окт 21, 10:43    [22389772]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
romaro
Member

Откуда:
Сообщений: 173
Если я правильно понял из скудной документации и ответа на этот вопрос, SETOF следует использовать в том случае, когда в базе уже есть объявленное представление или другая структура данных. Объявить новый тип записи в самой процедуре не получится. Поэтому в моем случае нужно использовать returns table:
автор
CREATE OR REPLACE FUNCTION public.setof_user_profile(p_json json)
RETURNS TABLE (col1 varchar, col2 varchar, col3 varchar)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY SELECT first_name, email, mobile_phone
FROM public.USER
WHERE id = (p_json ->> 'userKey')::integer;

RETURN;
END;
$function$
;


Однако она сваливает все данные в одну колонку. Есть ли способ разнести их на несколько?

К сообщению приложен файл. Размер - 12Kb
30 окт 21, 02:27    [22390235]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5022
romaro
Если я правильно понял из скудной документации и ответа на этот вопрос, SETOF следует использовать в том случае, когда в базе уже есть объявленное представление или другая структура данных. Объявить новый тип записи в самой процедуре не получится. Поэтому в моем случае нужно использовать returns table:
автор
CREATE OR REPLACE FUNCTION public.setof_user_profile(p_json json)
RETURNS TABLE (col1 varchar, col2 varchar, col3 varchar)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY SELECT first_name, email, mobile_phone
FROM public.USER
WHERE id = (p_json ->> 'userKey')::integer;

RETURN;
END;
$function$
;


Однако она сваливает все данные в одну колонку. Есть ли способ разнести их на несколько?


А покажите как вы эту процедуру вызываете?
90% что вы вызываете её неверно.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
30 окт 21, 10:06    [22390263]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
romaro
Member

Откуда:
Сообщений: 173
Maxim Boguk,

Вызов:
SELECT public.setof_user_profile('{"userKey":"21"}');


Еще раз код процедуры:
CREATE OR REPLACE FUNCTION public.setof_user_profile(p_json json)
 RETURNS TABLE(col1 character varying, col2 character varying, col3 character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
	RETURN QUERY SELECT first_name, email, mobile_phone
	FROM public.USER
	WHERE id = (p_json ->> 'userKey')::integer; --Приводим текст к числу

	RETURN;
END;
$function$
;


Вызываю через DBeaver, приходит строка вида:
автор
(Иван,mail@gmail.com,797777777777)


И структура таблицы:
CREATE TABLE public."user" (
	id serial4 NOT NULL,
	profile_state int4 NOT NULL DEFAULT 1,
	login varchar(50) NULL,
	password_hash varchar(500) NULL,
	password_hash_upd timestamp NULL,
	first_name varchar(100) NULL,
	second_name varchar(100) NULL,
	email varchar(254) NULL,
	email_confirmed bool NOT NULL DEFAULT false,
	mobile_phone varchar(16) NULL,
	mobile_phone_confirmed bool NOT NULL DEFAULT false,
	password_salt varchar(500) NULL,
	creation_date timestamptz NULL DEFAULT now(),
	CONSTRAINT user_pk PRIMARY KEY (id),
	CONSTRAINT user_fk FOREIGN KEY (profile_state) REFERENCES public.profile_state(id) ON UPDATE RESTRICT
);
30 окт 21, 10:54    [22390287]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5022
romaro,

Ну вот как я и написал неверно вызываете...
автор
SELECT public.setof_user_profile('{"userKey":"21"}');


надо
SELECT * FROM public.setof_user_profile('{"userKey":"21"}');


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
30 окт 21, 11:10    [22390293]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
romaro
Member

Откуда:
Сообщений: 173
Maxim Boguk,

Спасибо! Действительно не разобрался. А верно я понял, что setof только для заранее поименованных представлений? Я нигде не нашел примеров, как в теле функции объявить кастомный набор данных. То есть через setof можно вернуть либо колонку, либо сослаться на уже существующую таблицу/представление?

В итоге returns table как бы более универсален?
30 окт 21, 12:15    [22390306]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
Maxim Boguk
Member

Откуда: По разному.
Сообщений: 5022
romaro
Maxim Boguk,

Спасибо! Действительно не разобрался. А верно я понял, что setof только для заранее поименованных представлений? Я нигде не нашел примеров, как в теле функции объявить кастомный набор данных. То есть через setof можно вернуть либо колонку, либо сослаться на уже существующую таблицу/представление?

В итоге returns table как бы более универсален?


Как бы более универсален до тех пор пока вам не надо написать 10 функций возврашающих ВСЕ поля некоей таблицы причём заранее известно что список полей будет меняться.... и тогда setof tablename - очевидно удобнее.
А когда надо вернуть заданный список полей - тогда returns table.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
30 окт 21, 16:19    [22390362]     Ответить | Цитировать Сообщить модератору
 Re: Почему не удается вызвать функцию SETOF?  [new]
Павел Лузанов
Member

Откуда:
Сообщений: 845
Добавлю, что returns table это стандарт SQL. А setof это постгресовый вариант, который появился раньше.
А функционал и смысл одинаковый.

Если хотите через setof вернуть несколько столбцов, то это можно сделать через out параметры и setof record:
postgres=# create or replace function f (a out int, b out text) returns setof record 
postgres-# language sql as 'select g.x, random()::text from generate_series(1,3) as g(x)';
CREATE FUNCTION
postgres=# select * from f();
 a |         b          
---+--------------------
 1 | 0.2947739961214566
 2 | 0.432658960285881
 3 | 0.4376088315260205
(3 rows)
1 ноя 21, 20:17    [22391178]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить