Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Быстрый старт / 15 сообщений из 15, страница 1 из 1
12.06.2006, 19:47
    #33786416
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Ребята, пришел из мира Билли Гейтса :) тобиш с MSSQL. Научился писать функции, которые возвращают единственное значение. Никак не получается сделать функцию, которая бы возвращала набор строк из таблиц, причем разных.
Как правильно определить тип возвращаемых данных. Буду очень признателен, за простой пример. Заранее спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.06.2006, 19:50
    #33786417
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
PS в мире виндов можно просто было написать хранимую процедуру и потом ее дергать примерно так: exec my_stored_proc('param1', 34)

Или вообще присвоить рекордсету
set rs=cn.exec('my_stored_proc('param1', 34)')

Как такое же провернуть в постгре?

Что то туго у меня получается. :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.06.2006, 21:31
    #33786464
Igor Kozlov
Igor Kozlov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Если я правильно понял вопрос, то все что нужно это об'явить возвращаемое значение как SET OF и возвращать строки из функции RETURN NEXT.

А потом вызывать ф-цию: SELECT * FROM myfunc();

В доке все довольно прозрачно описано.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.06.2006, 21:33
    #33786467
ZemA
ZemA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
тынц , тынц , тынц
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2006, 14:32
    #33787687
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Получается, чтобы вернуть набор данных из разных таблиц (в функции сложный запрос с параметрами) я должен создать предварительно этот тип в Постргре?
Или можно как то по другому? Этож типов не насоздаешься :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2006, 15:45
    #33787946
Igor Kozlov
Igor Kozlov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Вот пример ф-ции (на pl/pgsql)

DECLARE
result text;
myrec RECORD;
BEGIN

FOR myrec IN <тут пишете сколь-угодно сложный селект из скольких угодно таблиц>
LOOP

<тут делаете что хотите с данными полученной записи>
<ну, например, result := myrec.поле; >

RETURN NEXT result; -- Возвращаете result для текущей записи
END LOOP;

END;

Цикл повторяется столько раз сколько получилось записей в результате вашего сложного запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2006, 16:17
    #33788064
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Спасибо 2 Igor!
Все стало на места более менее!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 09:20
    #33798672
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Не дает покоя вопрос производительности... Ведь крутить цикл по тысячам записей это ужасно... Не могу понять почему такие проблемы с возвращением набора записей из процедуры? Ведь если отрабатывает запрос с объединениями и тд. там работает оптимизатор, и пр. скорость максимальна. А тут циклы, ну куда это годиться? Либо чего то не понимаю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 09:33
    #33798698
ZemA
ZemA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
QuasarНе дает покоя вопрос производительности... Ведь крутить цикл по тысячам записей это ужасно... Не могу понять почему такие проблемы с возвращением набора записей из процедуры? Ведь если отрабатывает запрос с объединениями и тд. там работает оптимизатор, и пр. скорость максимальна. А тут циклы, ну куда это годиться? Либо чего то не понимаю.
специально для тебя
PostgreSQL 8.1.4 Documentation
32.4.4. SQL Functions as Table Sources

All SQL functions may be used in the FROM clause of a query, but it is particularly useful for functions returning composite types. If the function is defined to return a base type, the table function produces a one-column table. If the function is defined to return a composite type, the table function produces a column for each attribute of the composite type.

Here is an example:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES ( 1 ,  1 , 'Joe');
INSERT INTO foo VALUES ( 1 ,  2 , 'Ed');
INSERT INTO foo VALUES ( 2 ,  1 , 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $ 1 ;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo( 1 ) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
      1  |         1  | Joe     | JOE
( 1  row)

As the example shows, we can work with the columns of the function's result just the same as if they were columns of a regular table.

Note that we only got one row out of the function. This is because we did not use SETOF. That is described in the next section.
32.4.5. SQL Functions Returning Sets

When an SQL function is declared as returning SETOF sometype, the function's final SELECT query is executed to completion, and each row it outputs is returned as an element of the result set.

This feature is normally used when calling the function in the FROM clause. In this case each row returned by the function becomes a row of the table seen by the query. For example, assume that table foo has the same contents as above, and we say:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1 ;
$$ LANGUAGE SQL;

SELECT * FROM getfoo( 1 ) AS t1;[/b]

Then we would get:

 fooid | foosubid | fooname
-------+----------+---------
      1  |         1  | Joe
      1  |         2  | Ed
( 2  rows)


Currently, functions returning sets may also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and may be removed in future releases. The following is an example function returning a set from the select list:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $ 1 
$$ LANGUAGE SQL;

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
( 6  rows)

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
( 3  rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
( 5  rows)
In the last SELECT, notice that no output row appears for Child2, Child3, etc. This happens because listchildren returns an empty set for those arguments, so no result rows are generated.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 10:41
    #33798893
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Спасибо, конечно, но это я читал неоднократно. С одной таблицей все хорошо и просто, а когда запрос сложный, например, с объединениями типа SELECT f1.t1, f1.t2, f1.t3 ... FROM table1 as t1 INNER JOIN table2 as t2 ....
Нужно будет создавать тип с полями f1.t1, f1.t2 ... для каждой такой процецедуры?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 10:47
    #33798928
4321ё
4321ё
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
QuasarСпасибо, конечно, но это я читал неоднократно. С одной таблицей все хорошо и просто, а когда запрос сложный, например, с объединениями типа SELECT f1.t1, f1.t2, f1.t3 ... FROM table1 as t1 INNER JOIN table2 as t2 ....
Нужно будет создавать тип с полями f1.t1, f1.t2 ... для каждой такой процецедуры?сделайте вью по этому запросу - автоматом создастся тип. Дальше пользуйте этот тип в выводах. Или возвращайте SETOF RECORD, а при использовании каждый раз описывайте тип возврата.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 12:01
    #33799226
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
Чтобы не быть голословным:
Есть функция
----------------------------------------------
CREATE OR REPLACE FUNCTION get_people3()
RETURNS SETOF record AS
$BODY$declare
r record;
curs refcursor;

begin

open curs for select fam, im from people;
fetch curs into r;
return next r;
return;

end$BODY$
------------------------------------------------
вызываю в pgadmin ее сл. образом
select * from get_people3() as (fam varchar, im varchar);
Возвращает только первую запись.
Где грабли?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 12:10
    #33799269
ZemA
ZemA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
да нафига тебе plpgsql
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE OR REPLACE FUNCTION get_people3()
  RETURNS SETOF record AS
'
  select fam, im, eye_color, ...
    from people
    join eye_colors on people.eye_color_id = eye_colors.id
    ...
' language sql;


select * from get_people3() as (fam varchar, im varchar, eye_color varchar, ...);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.06.2006, 12:24
    #33799320
Quasar
Quasar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
ОК спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2006, 11:58
    #33816323
domanix
domanix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый старт
И вообще не забывайте про модификатор out у параметров функции ( pg 8 and above.). В этом случае не надо описывать тип - достаточно разрулить возвращаемыми параметрами..
к примеру

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create or replace function tablefunc (out id integer,out name text)
returns setof record language plpgsql as $$
declare rec record;
begin
 for rec in  select oid,relname from pg_class loop
   id=rec.oid;
   name=rec.name;
   return next;
 end loop;
end$$

select * from tablefunc() where name<'pg_';
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Быстрый старт / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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