Добрый день!
Решили перейти с Оракла на Postgre и здесь образовалась такая небольшая проблемка.

Есть тип: CREATE TYPE any_ids_table AS (any_ids_table integer[]);
Также имеется функция:
CREATE OR REPLACE FUNCTION cash_sum(atmids any_ids_table)
RETURNS SETOF cash_type_table AS ...

Как вызвать результат функции?
Пример вызова из Оракла:
select * from table(CASH_SUM(any_ids_table('100048','100045')));

vyegorov,

ERROR: function any_ids_table(unknown, unknown) does not exist
LINE 1: select * from CASH_SUM(any_ids_table('100048','100045'));
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

AndRam,

Также
select * from CASH_SUM('100048','100045));
ERROR: unterminated quoted string at or near "'100045));"
LINE 1: select * from CASH_SUM('100048','100045));

Не получается также если прописать ARRAY.

vyegorov,

vyegorov,

ERROR: function cash_sum(integer[]) does not exist
LINE 1: SELECT * FROM cash_sum('{100048, 100045}'::int[]);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

vyegorovAndRam,

Про работу с композитными типами в доках много .

В данном случае композитный тип не нужен, ПЖ поддерживает массивы и так :




У нас в Оракле просто практически вся система сделана через композитные типы.
Не хотелось бы от них избавляться - это очень много доп. работы.

vyegorovAndRam,

Композитный тип не есть массив, так что я бы рассмотрел вариант сделать всё как следует.

Вам надо выбрать значение нужного типа (`SELECT ...`), а потом уже выражение `...` подставить в вызов функции "как есть".

У меня это работает:

Соответственно:


Спасибо! Кажется заработало.

Добрый день!

Теперь проблема как запихать в функции переданный тип в запрос:

CREATE OR REPLACE FUNCTION cash_sum (atmids any_ids_table)
RETURNS SETOF CASH_TYPE_TABLE AS $body$
BEGIN
RETURN QUERY
SELECT sum(v.cash) AS cash , 0, get_currency_id(v.dic_currency__id)
FROM (
SELECT *
FROM ...
WHERE t.atm__id in (select atmids)
)
GROUP BY v.dic_currency__id;
END;
$body$
LANGUAGE PLPGSQL;

В данном примере выдает:
ERROR: operator does not exist: bigint = any_ids_table
LINE 10: AND t.atm__id in (select atmids)

Так читабельнее будет:
AndRamCREATE OR REPLACE FUNCTION cash_sum (atmids any_ids_table)
RETURNS SETOF CASH_TYPE_TABLE AS $body$
BEGIN
RETURN QUERY
SELECT sum(cash) AS cash , 0, get_currency_id(dic_currency__id)
FROM atm
WHERE atm__id in (select atmids)
GROUP BY dic_currency__id;
END;
$body$
LANGUAGE PLPGSQL;


Так же еще вопросы:
Есть ли какая-нибудь подробная документация по использованию композитных табличных типов и какие команды при этом используются, типа FIRST, LAST и т.д.

vyegorovAndRam,

Вот первая мысль — сначала мучаетесь, как бы это всё так свернуть, чтобы функцию вызвать.
Теперь мучаетесь, как бы развернуть это всё обратно.
И всё ради того, чтобы "поменьше кода править".

Мне кажется, что-то это странный подход...

Конечно это извращение, но альтернатива намного хуже. Подобных функций очень много и они используются и внутри кода и при вызове из приложения и в отчетах. Практически весь код в Oracle идет через подобные типы и внутри идет пошаговая обработка внутри типа, поэтому переделывать их неохота. К тому же хотелось бы знать работу postgre с подобными типами.

qwwqJonhsonпропущено...


Сочувствую, мы тоже рассматривали перевод части функционала, но выяснилось, что мороки пока что больше, чем плюсов.

Решили подождать 10-ю версию в надежде, что п-г несколько повзрослеет
ф топег приглашаедся г--н Лавров со своим бессмертным мемом

Проблема в том что если не перейдем с оракла на постгре заказчик откажется от программы. :(

vyegorovAndRam,

По существу:
т.к. вы только запрос вызываете, то вам не нужен plpgsql, тут функции на sql будет достаточно. sql предпочтительней, т.к. планировщик его "видит" и может оптимизировать, любой другой PL -- чёрный ящик

конструкции ANY / ALL / SOME в ПЖ заточены под массивы, попробуйте что-то вроде: WHERE t.atm__id = ANY ( ($1).any_ids_table ) -- вам надо обратиться к колонке в записи, коей является сложный тип.


1)
CREATE TYPE cash_type_table AS
(cash_type_table cash_type[]);
CREATE TYPE cash_type AS
(cash_sum bigint,
cash_sum2 bigint,
dic_currency__id bigint);
CREATE TYPE any_ids_table AS
(any_ids_table integer[]);

2)
CREATE OR REPLACE FUNCTION cash_sum3(atmids any_ids_table)
RETURNS SETOF CASH_TYPE_TABLE AS $body$
BEGIN
RETURN QUERY
SELECT 1,2,3
FROM atm
WHERE id = ANY ( ($1).any_ids_table );
END;
$body$
LANGUAGE PLPGSQL;

3) SELECT * FROM cash_sum3( (ROW(ARRAY[ 100048, 100045 ]))::any_ids_table );

4) В результате такая ошибка.
ERROR: structure of query does not match function result type
DETAIL: Returned type integer does not match expected type cash_type[] in column 1.
CONTEXT: PL/pgSQL function cash_sum3(any_ids_table) line 3 at RETURN QUERY

********** Ошибка **********

ERROR: structure of query does not match function result type
SQL-состояние: 42804
Подробности: Returned type integer does not match expected type cash_type[] in column 1.
Контекст: PL/pgSQL function cash_sum3(any_ids_table) line 3 at RETURN QUERY

qwwqAndRamпропущено...


1)





Не работает. При создании функции ошибка:

ERROR: return type mismatch in function declared to return cash_type
DETAIL: Final statement returns integer instead of bigint at column 1.
CONTEXT: SQL function "cash_sum3"

********** Ошибка **********

ERROR: return type mismatch in function declared to return cash_type
SQL-состояние: 42P13
Подробности: Final statement returns integer instead of bigint at column 1.
Контекст: SQL function "cash_sum3"