Sql-функция на Pl/sql
Есть БД PostgreSQL 7.4.7
Необходимо написать функцию, которая бы возвращала всех игроков зарегистрировавшихся сегодня.
Вот листинг:
CREATE FUNCTION get_usr() RETURNS RECORD as '
DECLARE
r1 RECORD;
BEGIN
SELECT userid, login, regtime INTO r1 FROM users WHERE regtime>CURRENT_DATE;
RETURN r1;
END;
' LANGUAGE 'plpgsql';
Тип данных RECORD насколько я понимаю это просто одна запись, а функция возвращает по сути таблицу. Как написать такую функцию, которая бы возвращала таблицу?
Заранее благодарен.
PS В PostgreSQL Documentation я так и не смог ничего найти по этому поводу. Там подобного рода функции вообще не рассматриваются.
Необходимо написать функцию, которая бы возвращала всех игроков зарегистрировавшихся сегодня.
Вот листинг:
CREATE FUNCTION get_usr() RETURNS RECORD as '
DECLARE
r1 RECORD;
BEGIN
SELECT userid, login, regtime INTO r1 FROM users WHERE regtime>CURRENT_DATE;
RETURN r1;
END;
' LANGUAGE 'plpgsql';
Тип данных RECORD насколько я понимаю это просто одна запись, а функция возвращает по сути таблицу. Как написать такую функцию, которая бы возвращала таблицу?
Заранее благодарен.
PS В PostgreSQL Documentation я так и не смог ничего найти по этому поводу. Там подобного рода функции вообще не рассматриваются.
Цитата:
Originally posted by zorkk
Есть БД PostgreSQL 7.4.7
Необходимо написать функцию, которая бы возвращала всех игроков зарегистрировавшихся сегодня.
Вот листинг:
CREATE FUNCTION get_usr() RETURNS RECORD as '
DECLARE
r1 RECORD;
BEGIN
SELECT userid, login, regtime INTO r1 FROM users WHERE regtime>CURRENT_DATE;
RETURN r1;
END;
' LANGUAGE 'plpgsql';
Тип данных RECORD насколько я понимаю это просто одна запись, а функция возвращает по сути таблицу. Как написать такую функцию, которая бы возвращала таблицу?
Заранее благодарен.
PS В PostgreSQL Documentation я так и не смог ничего найти по этому поводу. Там подобного рода функции вообще не рассматриваются.
Есть БД PostgreSQL 7.4.7
Необходимо написать функцию, которая бы возвращала всех игроков зарегистрировавшихся сегодня.
Вот листинг:
CREATE FUNCTION get_usr() RETURNS RECORD as '
DECLARE
r1 RECORD;
BEGIN
SELECT userid, login, regtime INTO r1 FROM users WHERE regtime>CURRENT_DATE;
RETURN r1;
END;
' LANGUAGE 'plpgsql';
Тип данных RECORD насколько я понимаю это просто одна запись, а функция возвращает по сути таблицу. Как написать такую функцию, которая бы возвращала таблицу?
Заранее благодарен.
PS В PostgreSQL Documentation я так и не смог ничего найти по этому поводу. Там подобного рода функции вообще не рассматриваются.
Попробуй как в MSSQL:
CREATE function get_usr()
RETURNS @new_users TABLE (user_id varchar(10), user_name varchar(50))
AS
BEGIN
DEFINE @user_id varchar(10), @user_name varchar(50)
-- код функции
-- в конце вставка в таблицу
INSERT @new_users (user_id, user_name) select @user_id, @user_name
RETURN
END
Может с синтаксисом не все верно, но главное же идея? ;)
MS SQL далек от стандарта. Делается все не так. Выложу ответ на свой же вопрос - может кому прригодится.
Для начала стоит создать новый тип данных (это достаточно удобно).
Далее пишем собственно функцию:
В PostgreSQL 7.4.7 эта функция замечательно работает. Вероятно не очень элегантно (я думаю, что не нужно в цикле присваивание писать, а сразу возвращать строки по одной), но работает.
Для начала стоит создать новый тип данных (это достаточно удобно).
CREATE TYPE users_type AS (userid int, login text, user_name text);
Далее пишем собственно функцию:
CREATE FUNCTION get_users() RETURNS SETOF users_type AS '
DECLARE
r users_type%ROWTYPE;
BEGIN
FOR Row IN SELECT * FROM users WHERE regtime>CURRENT_DATE LOOP
r.userid=Row.userid;
r.login=Row.login;
r.user_name=Row.user_name;
RETURN NEXT r;
END LOOP;
RETURN;
END;
' LANGUAGE PLPGSQL;
В PostgreSQL 7.4.7 эта функция замечательно работает. Вероятно не очень элегантно (я думаю, что не нужно в цикле присваивание писать, а сразу возвращать строки по одной), но работает.
на мой взгляд, вполне пожно обойтись вьюшкой.
Цитата:
Originally posted by Erast
на мой взгляд, вполне пожно обойтись вьюшкой.
на мой взгляд, вполне пожно обойтись вьюшкой.
К сожалению, нельзя. Нагрузка на БД весьма велика, таблицы очень объемные и наличие 10-15 представлений могут ее просто убить. К тому скорость отработки запросов в БД критична - нельзя допустить, чтобы запросы слишком долго ожидали.
Цитата:
Originally posted by zorkk
К сожалению, нельзя. Нагрузка на БД весьма велика, таблицы очень объемные и наличие 10-15 представлений могут ее просто убить. К тому скорость отработки запросов в БД критична - нельзя допустить, чтобы запросы слишком долго ожидали.
К сожалению, нельзя. Нагрузка на БД весьма велика, таблицы очень объемные и наличие 10-15 представлений могут ее просто убить. К тому скорость отработки запросов в БД критична - нельзя допустить, чтобы запросы слишком долго ожидали.
Каким образом 10-15 вьюшек могут убить базу? - это раз.
Если у тебя зарегилось N пользователей, то что лучше -- сделать N запросов, выбирая их по одному или один запрос, который выберет их всех? - это два.
И вообще, какая разница (кроме указанной выше) между твоей функцией и вьюшкой: что там, что там производится запрос SELECT? - это три
Эта функция лишь пример. Для того, чтобы развеять сомнения относительно вьюшек приведу такую инфу: на текущий час в самой маленькой таблице содержится 970 000 записей, в самой большой 19 миллионов. Поскольку сайт тесно интегрирован с БД, то загрузка любой страницы приводит к запуску 5-ти SQL-запросов (сделано из сообжаний безопасности). Посещаемость сайта более 9000 в день.
Вот пример реальной функции.
Вот пример реальной функции.
Код:
CREATE FUNCTION get_games_result(integer) RETURNS SETOF game_result AS '
DECLARE
r game_result%ROWTYPE;
Row gamestatistics%ROWTYPE;
UID ALIAS FOR $1;
BEGIN
IF UID=0 THEN
SELECT COUNT(*) as num_bets, SUM(balancechange) as balancechange, SUM(bonuschange) as bonuschange, gameid, paytype INTO TEMPORARY tab17 FROM pays WHERE paytype<=2 AND gameid<>0 GROUP BY gameid, paytype;
FOR Row IN SELECT * FROM gamestatistics LOOP
r.gameid = Row.gameid;
r.game_name = Row.name;
r.num_games = (SELECT num_bets FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_in = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.bonus_in = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_out = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_out = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_plus = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=2 AND gameid=Row.gameid);
RETURN NEXT r;
END LOOP;
RETURN;
ELSE
SELECT p.* INTO TEMPORARY tab17 FROM pays p, sessions s WHERE s.userid=UID AND s.sessionid=p.sessionid;
FOR Row IN SELECT * FROM gamestatistics LOOP
r.gameid = Row.gameid;
r.game_name = Row.name;
r.num_games = (SELECT COUNT(*) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_in = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.bonus_in = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_out = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_out = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_plus = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=2 AND gameid=Row.gameid);
RETURN NEXT r;
END LOOP;
RETURN;
END IF;
END;
' LANGUAGE PLPGSQL;
DECLARE
r game_result%ROWTYPE;
Row gamestatistics%ROWTYPE;
UID ALIAS FOR $1;
BEGIN
IF UID=0 THEN
SELECT COUNT(*) as num_bets, SUM(balancechange) as balancechange, SUM(bonuschange) as bonuschange, gameid, paytype INTO TEMPORARY tab17 FROM pays WHERE paytype<=2 AND gameid<>0 GROUP BY gameid, paytype;
FOR Row IN SELECT * FROM gamestatistics LOOP
r.gameid = Row.gameid;
r.game_name = Row.name;
r.num_games = (SELECT num_bets FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_in = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.bonus_in = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_out = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_out = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_plus = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=2 AND gameid=Row.gameid);
RETURN NEXT r;
END LOOP;
RETURN;
ELSE
SELECT p.* INTO TEMPORARY tab17 FROM pays p, sessions s WHERE s.userid=UID AND s.sessionid=p.sessionid;
FOR Row IN SELECT * FROM gamestatistics LOOP
r.gameid = Row.gameid;
r.game_name = Row.name;
r.num_games = (SELECT COUNT(*) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_in = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.bonus_in = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=0 AND gameid=Row.gameid);
r.real_out = (SELECT SUM(balancechange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_out = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=1 AND gameid=Row.gameid);
r.bonus_plus = (SELECT SUM(bonuschange) FROM tab17 WHERE paytype=2 AND gameid=Row.gameid);
RETURN NEXT r;
END LOOP;
RETURN;
END IF;
END;
' LANGUAGE PLPGSQL;
И снова:
Ты SQL-запросы делаешь как во вьюшке, так и в функции, так какая разница, кроме того, что в первом случае ты делаешь ОДИН запрос, а во втором 9000.
Ты SQL-запросы делаешь как во вьюшке, так и в функции, так какая разница, кроме того, что в первом случае ты делаешь ОДИН запрос, а во втором 9000.
Вьюшка будет пересчитываться постоянно, а функцию я буду вызывать 2-3 раза в день, когда потребуется отчет и статистика. В таблицы, которые задействованы в функции, каждую секунду добавляется до 100 записей - производительность просядет. Особенно если учесть что таких отчетов по статистике у нас около 40 видов сейчас.