ReSQL.ru
     
powered by simpleCommunicator - 2.0.40     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Почему не удается вызвать функцию SETOF?
10 сообщений из 10, страница 1 из 1
Почему не удается вызвать функцию SETOF?
    #40107917
romaro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
romaro
Гость
Запрашиваю из таблицы три текстовые колонки:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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:
Код: plsql
1.
SELECT public.setof_user_profile('{"userKey":"21"}');
...
Рейтинг: 0 / 0
29.10.2021, 04:04
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40107938
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
romaro,

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
29.10.2021, 09:16
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40107969
О-О-О
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
О-О-О
Гость
romaro,



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

jsonb ->> text → text
Извлекает поле JSON-объекта по заданному ключу, в виде значения text.
'{"a":1,"b":2}'::json ->> 'b' → 2
...
Рейтинг: 0 / 0
29.10.2021, 10:43
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108236
romaro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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$
;


Однако она сваливает все данные в одну колонку. Есть ли способ разнести их на несколько?
...
Рейтинг: 0 / 0
30.10.2021, 02:27
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108243
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
...
Рейтинг: 0 / 0
30.10.2021, 10:06
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108248
romaro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
romaro
Гость
Maxim Boguk,

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



Еще раз код процедуры:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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)

И структура таблицы:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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
);
...
Рейтинг: 0 / 0
30.10.2021, 10:54
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108249
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
romaro,

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

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



--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
30.10.2021, 11:10
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108254
romaro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
romaro
Гость
Maxim Boguk,

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

В итоге returns table как бы более универсален?
...
Рейтинг: 0 / 0
30.10.2021, 12:15
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108279
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
romaro
Maxim Boguk,

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

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


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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
30.10.2021, 16:19
| Ответить | Цитировать | Написать  
Почему не удается вызвать функцию SETOF?
    #40108704
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Добавлю, что returns table это стандарт SQL. А setof это постгресовый вариант, который появился раньше.
А функционал и смысл одинаковый.

Если хотите через setof вернуть несколько столбцов, то это можно сделать через out параметры и setof record:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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)
...
Рейтинг: 0 / 0
01.11.2021, 20:17
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Почему не удается вызвать функцию SETOF?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=40108279&tid=1993801]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 255ms
total:  376ms
созд. / загр.: 376ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]