На главную
ПРАВИЛА FAQ Помощь Участники Календарь Избранное DigiMania RSS
msm.ru
! информация о разделе
user posted imageДанный раздел предназначается для обсуждения вопросов использования баз данных, за исключением составления запросов на SQL. Для этого выделен специальный раздел. Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Chow, Bas, MIF, JoeUser
  
> Немного вопросов по функциям
    Доброго времени суток!

    Решил часть часто-используемого кода вынести во VIEW, но т.к. ранее этим не пользовался, полез в сеть за объяснялками и примерами. Во многих местах советуют в случае необходимости параметров писать функцию. Ладно, посмотрел как это делается, написал вот такое:
    Скрытый текст
    ExpandedWrap disabled
      CREATE OR REPLACE FUNCTION public."ClientOnService" (
        datebegin date,
        dateend date
      )
      RETURNS TABLE (
        id integer,
        familyid integer,
        age integer,
        sex integer,
        ischild integer,
        isparent integer
      ) AS
      $body$
      BEGIN
        RETURN QUERY
        SELECT
          x."Id"::INTEGER,
          x."FamilyId"::INTEGER,
          x."Age"::INTEGER,
          x."Sex"::INTEGER,
          CASE WHEN
            SUM(CASE WHEN COALESCE(x."Childs1",0)>0 THEN 1 ELSE 0 END) = 0 AND
            SUM(CASE WHEN COALESCE(fa2."Relation",0)>0 THEN 1 ELSE 0 END) = 0 AND
            x."Age" < 18
            THEN 1::INTEGER ELSE 0::INTEGER END AS "IsChild",
          CASE WHEN
            SUM(CASE WHEN COALESCE(x."Childs1",0)>0 THEN 1 ELSE 0 END) > 0 AND
            SUM(CASE WHEN COALESCE(fa2."Relation",0)>0 THEN 1 ELSE 0 END) = 0
            THEN 1::INTEGER ELSE 0::INTEGER END AS "IsParent"
        FROM (
          SELECT
            c."Id",
            c."FamilyId",
            CASE WHEN c."YearOfBirth" < 150 THEN c."YearOfBirth" ELSE (EXTRACT(year FROM now()) - c."YearOfBirth")::INTEGER END AS "Age",
            c."Gender" AS "Sex",
            c."Gender",
            CASE WHEN ((COALESCE(fa1."Relation",0)>0) AND (fa1."ClientOne" = c."Id")) THEN fa1."ClientTwo"
                 WHEN ((COALESCE(fa1."Relation",0)>0) AND (fa1."ClientTwo" = c."Id")) THEN fa1."ClientOne" ELSE 0 END AS "Childs1"
          FROM
            "Clients" AS c
          LEFT JOIN
            "Family" AS fa1 ON
              (fa1."ClientOne" = c."Id" AND fa1."Relation" IN (15,16,19,20)) OR
              (fa1."ClientTwo" = c."Id" AND fa1."Relation" IN (10,21,22,30))
          WHERE
            c."FamilyId" IN (
              SELECT
                c."FamilyId"
              FROM
                "Service" AS s,
                "Service2Clients" AS sc,
                "Clients" AS c
              WHERE
                c."Id" = sc."Client" AND
                sc."Service" = s."Id" AND
                s."Date" BETWEEN DateBegin AND DateEnd AND
                s."State" > 0 AND
                sc."Present"
            )
        ) AS x
        LEFT JOIN
          "Family" AS fa2 ON
            (fa2."ClientOne" = x."Childs1" AND fa2."Relation" IN (15,16,19,20)) OR
            (fa2."ClientTwo" = x."Childs1" AND fa2."Relation" IN (10,21,22,30))
        GROUP BY
          x."Id", x."FamilyId", x."Age", x."Sex";
      END;
      $body$
      LANGUAGE 'plpgsql'
      VOLATILE
      CALLED ON NULL INPUT
      SECURITY INVOKER
      COST 100 ROWS 1000;

    Запустил в SQL-менеджере, запросило две даты и выдала результат. Все устроило.
    Ну собственно, возникли вопросы:

    1. Эта функция и есть "хранимая процедура" или это еще какой-то дополнительный механизм?
    2. В настройках видел еще такой описатель LANGUAGE 'sql', чем это лучше/хуже чем LANGUAGE 'plpgsql'? Или вообще не заморачиваться?
    3. При написании было много ругни на возвращаемые типы, пришлось каждому добавить "приведение" в виде ::INTEGER. Вопрос какого лешего если они и так целые? Или где я ошибаюсь?
    4. Написал изначально для помещения в аргументы констант, получаемых из GUI. Но, почитавши далее, понял, что данную функцию в принципе можно использовать как обычную таблицу. Если это действительно так, подскажите плс как, к примеру, сделать FULL OUTER JOIN с другой таблицей, чтобы использовались поля с другой таблицы в качестве аргументов. Табличка примера ниже.

    ExpandedWrap disabled
      CREATE TABLE public."Period" (
        "Id" SERIAL,
        "StartDate" DATE NOT NULL,
        "StopDate" DATE NOT NULL,
        CONSTRAINT "Period_pkey" PRIMARY KEY("Id")
      ) WITHOUT OIDS;
    Мои программные ништякиhttp://majestio.info
      Цитата JoeUser @
      Эта функция и есть "хранимая процедура" или это еще какой-то дополнительный механизм?

      Функция не есть "хранимая процедура".
      В ф-ции нельзя использовать DML операторы. В вашем случае возвращаемое значение может далека от оригинала, можно что то умножить на что то и тд.
      Цитата JoeUser @
      В настройках видел еще такой описатель LANGUAGE 'sql', чем это лучше/хуже чем LANGUAGE 'plpgsql'? Или вообще не заморачиваться?

      Зависит от БД.

      Добавлено
      Цитата JoeUser @
      часто-используемого кода вынести во VIEW

      чем не устраивает обычный CREATE VIEW ?
      Цель - ничто , процесс - все.
        Цитата Bas @
        чем не устраивает обычный CREATE VIEW ?


        Да я с ними не работал ни разу, написал чего "нашлось" в сети по-быстрому :rolleyes: А если это сделать с помощью CREATE VIEW будут какие дополнительные "полезности", и по скорости работы будет как, по сравнению со сделанным?
        Мои программные ништякиhttp://majestio.info
          Цитата JoeUser @
          CREATE VIEW будут какие дополнительные "полезности", и по скорости работы будет как, по сравнению со сделанным?

          Вьюха компилица один раз и план запроса храниться постоянно.
          Можно к вьюхе обращаться и работать как к таблицей.
          Можно создавать модифицируемые представления (при соблюдении определенных условий, в вашем случае они не соблюдены).
          Сообщение отредактировано: Bas -
          Цель - ничто , процесс - все.
            Цитата Bas @
            Вьюха компилица один раз и план запроса храниться постоянно.


            Народ пишет, что VIEW не поддерживает параметры, это верно?
            Мои программные ништякиhttp://majestio.info
              Цитата JoeUser @
              что VIEW не поддерживает параметры, это верно?

              Да.
              Но запрос типа
              ExpandedWrap disabled
                select * from myVIEW  where tram=tatam

              Вполне возможен.
              Цель - ничто , процесс - все.
                Цитата Bas @

                Но запрос типа
                ExpandedWrap disabled
                  select * from myVIEW  where tram=tatam

                Вполне возможен.


                Я так понимаю, что 'tram' должен быть одним из полей, возвращаемых VIEW? В моем случае "параметры" используются глубоко в подзапросах. Наверное всеж использование функции будет более удобным, если вообще единственным решением?
                Мои программные ништякиhttp://majestio.info
                1 пользователей читают эту тему (1 гостей и 0 скрытых пользователей)
                0 пользователей:


                Рейтинг@Mail.ru
                [ Script Execution time: 0,1033 ]   [ 15 queries used ]   [ Generated: 16.09.19, 14:24 GMT ]