[SQL] PostgreSQL는 테이블 함수에서 주문 / 제한 파라미터

PostgreSQL는 테이블 함수에서 주문 / 제한 파라미터

나는 간단한 SQL SELECT 문을 수행하는 SQL 함수를 가지고 :

CREATE OR REPLACE FUNCTION getStuff(param character varying)
  RETURNS SETOF stuff AS
$BODY$
    select *
    from stuff
    where col = $1
$BODY$
  LANGUAGE sql;

지금은이 같은이 함수를 호출하고 있습니다 :

select * from getStuff('hello');

내가 주문 필요에 의해 순서 및 제한 조항으로 결과를 제한하는 경우 내 옵션은 무엇입니까?

나는이 같은 쿼리를 추측 :

select * from getStuff('hello') order by col2 limit 100;

테이블 물건의 모든 행이 기능 getStuff에 의해 반환 만 다음 순서 및 제한에 의해 슬라이스되기 때문에 매우 효율적으로하지 않을 것입니다.

내가 맞다하더라도 그러나,는 SQL 언어 함수의 인수로 순서를 전달하는 방법을 쉬운 방법이 없습니다. 만 값은 SQL 문을의하지 부분, 전달 될 수 있습니다.

또 다른 옵션은 쿼리를 생성 및 실행을 통해 실행 할 수 plpgsql 언어에서 함수를 만드는 것입니다. 그러나이 역시 아주 좋은 방법이 아니다.

그래서,이 달성의 다른 방법이있다? 또는 어떤 옵션를 선택할 것인가? 주문 / 기능, 또는 plpgsql 외부 제한?

내가 PostgreSQL은 9.1을 사용하고 있습니다.

나는이 같은 CREATE FUNCTION 문 수정 :

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying)
  RETURNS SETOF stuff AS
$BODY$
    select t.*
    from stuff t
    where col = $1
    ORDER BY
        CASE WHEN $2 = 'parent' THEN t.parent END,
        CASE WHEN $2 = 'type' THEN t."type" END, 
        CASE WHEN $2 = 'title' THEN t.title END

$BODY$
  LANGUAGE sql;

이 예외 :

해결법

1. ==============================

   1.plpgsql 기능 아무것도 잘못이있다. 그것은 좀 더 복잡한 아무것도 가장 우아하고 가장 빠른 솔루션입니다. plpgsql 기능이 중첩 될 때 질의 플래너 또는 더 느리게하지 않을 수도 외부 쿼리의 컨텍스트에 포함 된 코드를 최적화 할 수 없기 때문에 성능이 저하 될 수있는 유일한 상황이다. 나중에 대답 자세한 내용 :

   plpgsql 기능 아무것도 잘못이있다. 그것은 좀 더 복잡한 아무것도 가장 우아하고 가장 빠른 솔루션입니다. plpgsql 기능이 중첩 될 때 질의 플래너 또는 더 느리게하지 않을 수도 외부 쿼리의 컨텍스트에 포함 된 코드를 최적화 할 수 없기 때문에 성능이 저하 될 수있는 유일한 상황이다. 나중에 대답 자세한 내용 :

   이 경우 쿼리에서 CASE 절을 많이보다 훨씬 간단하다 :

   CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int)
     RETURNS SETOF stuff AS
   $func$
   BEGIN
      RETURN QUERY EXECUTE '
         SELECT *
         FROM   stuff
         WHERE  col = $1
         ORDER  BY ' || quote_ident(_orderby) || ' ASC
         LIMIT  $2'
      USING _param, _limit;
   END
   $func$  LANGUAGE plpgsql;
   

   요구:

   SELECT * FROM get_stuff('hello', 'col2', 100);
   

   당신은 단지 부모를 반환하기 때문에 편집 한 후 두 번째 기능은하지 작업, 반환 형식은 SETOF 물건을 선언 할 수있다. 당신은 당신이 원하는 반환 형식을 선언 할 수 있지만, 실제 리턴 값은 선언과 일치해야합니다. 당신은 그것을 위해 RETURNS 표를 사용할 수도 있습니다.

2. ==============================

   2.함수는 (데이터베이스를 수정하지 않습니다) 안정 경우, 쿼리 플래너는 일반적으로 인라인 것입니다. 따라서, ( 'X')를 제한 getStuff 내부 것처럼 동일한 쿼리 계획을 생성한다 LIMIT 10 () * getStuff에서 선택을하고.

   함수는 (데이터베이스를 수정하지 않습니다) 안정 경우, 쿼리 플래너는 일반적으로 인라인 것입니다. 따라서, ( 'X')를 제한 getStuff 내부 것처럼 동일한 쿼리 계획을 생성한다 LIMIT 10 () * getStuff에서 선택을하고.

   그러나, 당신은 같은 그것을 선언 PG 함수가 안정을 알려줄 필요가 :

   CREATE OR REPLACE FUNCTION getStuff(param varchar)
   RETURNS setof STUFF
   LANGUAGE SQL
   STABLE
   AS $$ ... $$;
   

   이제 getStuff로부터 SELECT *를 EXPLAIN하고 ( 'X') 것에 해당하는 쿼리를 작성하는 동일한 쿼리 계획을 생성한다 LIMIT 1.

   인라인은 함수 외부 ORDER BY 절을 위해 일해야한다. 당신이 순서를하여 결정하는 기능을 매개 변수화하기를 원한다면, 당신은 또한 정렬 방향을 제어하기 위해 다음과 같이 그것을 할 수 있습니다 :

   CREATE FUNCTION sort_stuff(sort_col TEXT, sort_dir TEXT DEFAULT 'asc')
   RETURNS SETOF stuff
   LANGUAGE SQL
   STABLE
   AS $$
       SELECT *
       FROM stuff
       ORDER BY
         -- Simplified to NULL if not sorting in ascending order.
         CASE WHEN sort_dir = 'asc' THEN
             CASE sort_col
                 -- Check for each possible value of sort_col.
                 WHEN 'col1' THEN col1
                 WHEN 'col2' THEN col2
                 WHEN 'col3' THEN col3
                 --- etc.
                 ELSE NULL
             END
         ELSE
             NULL
         END
         ASC,
   
         -- Same as before, but for sort_dir = 'desc'
         CASE WHEN sort_dir = 'desc' THEN
             CASE sort_col
                 WHEN 'col1' THEN col1
                 WHEN 'col2' THEN col2
                 WHEN 'col3' THEN col3
                 ELSE NULL
             END
         ELSE
             NULL
         END
         DESC
   $$;
   

   만큼 sort_col로하고 sort_dir 쿼리 내에서 일정 쿼리 플래너가 쿼리를 찾고 장황를 단순화 할 수있을 것입니다

   SELECT *
   FROM stuff
   ORDER BY <sort_col> <sort_dir>
   

   어떤 당신은 Explain을 사용하여 확인할 수 있습니다.

3. ==============================

   3.이런 식으로 뭔가를 시도 할 수 귀하가 순서에로 :

   이런 식으로 뭔가를 시도 할 수 귀하가 순서에로 :

   SELECT
       <column list>
   FROM
       Stuff
   WHERE
       col1 = $1
   ORDER BY
       CASE $2
           WHEN 'col1' THEN col1
           WHEN 'col2' THEN col2
           WHEN 'col3' THEN col3
           ELSE col1  -- Or whatever your default should be
       END
   

   당신은 어떤 데이터 형식 변환을해야 할 수도 있습니다되도록 CASE 결과 일치의 데이터 유형의 모든. 그냥 문자열로 수치를 변환하는 방법에 대한주의 - 당신은 그들이 제대로 주문하기 위해 앞에 추가 0으로해야합니다. 동일 날짜 / 시간 값에 간다. 년 월 다음에 한 형식으로 주문 일 등 다음

   나는 SQL 서버에 이런 짓을, 그러나 결코 PostgreSQL을, 그리고이 안된 그래서 나는,이 시스템의 PostgreSQL의 사본이없는했습니다.

4. ==============================

   4.당신은 아무 문제없이 함수의 인수로 제한 값을 전달할 수 있습니다. 당신이 CASE 문와 함께 데르 BY를 사용할 수있는 주문에 관해서는. 이것은 불행하게도 같은 작동하지 않습니다

   당신은 아무 문제없이 함수의 인수로 제한 값을 전달할 수 있습니다. 당신이 CASE 문와 함께 데르 BY를 사용할 수있는 주문에 관해서는. 이것은 불행하게도 같은 작동하지 않습니다

   ORDER BY CASE condition_variable
   WHEN 'asc' THEN column_name ASC
   ELSE column_name DESC
   END;
   

from https://stackoverflow.com/questions/8139618/postgresql-parameterized-order-by-limit-in-table-function by cc-by-sa and MIT license