funciona - postgresql pdf

PostgreSQL parametrizado por orden/límite en la función de tabla

Question

Tengo una función sql que hace una instrucción de selección SQL simple:

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

Por ahora estoy invocando esta función de esta manera:

select * from getStuff(''hello'');

¿Cuáles son mis opciones si necesito ordenar y limitar los resultados con las cláusulas order by y limit ?

Supongo que una consulta como esta:

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

no sería muy eficiente, porque todas las filas de la tabla serán devueltas por la función getStuff y solo entonces ordenadas y cortadas por límite.

Pero incluso si estoy en lo cierto, no hay una manera fácil de pasar el orden por argumento de una función de lenguaje sql. Solo se pueden pasar valores, no partes de la instrucción SQL.

Otra opción es crear la función en lenguaje plpgsql , donde es posible construir la consulta y ejecutarla a través de EXECUTE . Pero este tampoco es un enfoque muy agradable.

Entonces, ¿hay algún otro método para lograr esto? ¿O qué opción elegirías? Ordenar / limitar fuera de la función, o plpgsql?

Estoy usando postgresql 9.1.

Editar

Modifiqué la declaración CREATE FUNCTION así:

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;

Esto arroja:

ERROR: el tipo de caracteres de CASE varía y el entero no puede coincidir ŘÁDKA 13: CUANDO $ 1 = ''parent'' THEN t.parent

La tabla de stuff se ve así:

CREATE TABLE stuff
    (
      id integer serial,
      "type" integer NOT NULL,
      parent integer,
      title character varying(100) NOT NULL,
      description text,
      CONSTRAINT "pkId" PRIMARY KEY (id),
    )

Edit2

He leído mal el código Dems. Lo he corregido para preguntar. Este código me funciona.

Answer 1

En cuanto a ORDER BY podrías intentar algo como esto:

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

Es posible que tenga que realizar algunas conversiones de tipos de datos para que todos los tipos de datos en el resultado CASE coincidan. Solo tenga cuidado con la conversión de números a cadenas: tendrá que anteponer 0 para que se ordenen correctamente. Lo mismo ocurre con los valores de fecha / hora. Ordenar por un formato que tiene el año seguido por el mes seguido por el día, etc.

He hecho esto en SQL Server, pero nunca en PostgreSQL, y no tengo una copia de PostgreSQL en esta máquina, por lo que no se ha probado.

Answer 2

Puede pasar el valor límite como un argumento de función sin ningún problema. En cuanto a los pedidos, puede usar ODER BY en combinación con la sentencia CASE. Desafortunadamente, esto no funcionará para algo como

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

Answer 3

Si su función es estable (no modifica la base de datos), el planificador de consultas generalmente lo alineará . Por lo tanto, haciendo SELECT * FROM getStuff(''x'') LIMIT 10 producirá el mismo plan de consulta como si el límite estuviera dentro de getStuff() .

Sin embargo, necesita decirle a PG que su función es estable al declararlo como tal:

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

Ahora haciendo EXPLAIN SELECT * FROM getStuff(''x'') LIMIT 1 debería producir el mismo plan de consulta que escribiría la consulta equivalente.

La línea de entrada también debería funcionar para las cláusulas ORDER BY fuera de la función. Pero si quería parametrizar la función para determinar el orden, podría hacerlo así para controlar también la dirección de clasificación:

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
$$;

Siempre que sort_col y sort_dir sean constantes dentro de la consulta, el planificador de consultas debería ser capaz de simplificar la consulta de aspecto detallado para

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

que puedes verificar usando EXPLAIN .

Answer 4

No hay nada de malo con una función plpgsql . Es la solución más elegante y rápida para cualquier cosa un poco más compleja. La única situación donde el rendimiento podría sufrir es cuando anida las funciones plpgsql porque el planificador de consultas no puede optimizar aún más el código dentro del contexto de la consulta externa, lo que puede o no hacerlo más lento.
Más detalles en esta última respuesta:

• Diferencia entre el lenguaje sql y el lenguaje plpgsql en las funciones de PostgreSQL

En este caso, es mucho más simple que muchas cláusulas CASE en una consulta:

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _limit int)
  RETURNS SETOF stuff AS
$BODY$
BEGIN

RETURN QUERY EXECUTE ''
    SELECT *
    FROM   stuff
    WHERE  col = $1
    ORDER  BY '' || quote_ident(_orderby) || ''
    LIMIT  $2''
USING _param, _limit;

END;
$BODY$
  LANGUAGE plpgsql;

Llamada:

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

Notas

• Utilice RETURN QUERY EXECUTE para devolver los resultados de la consulta de una vez.
• Utilice quote_ident () para los identificadores para salvaguardar contra SQLi.
• Use USING para entregar los valores de los parámetros para evitar el lanzamiento, las citas y SQLi una vez más.
• Tenga cuidado de no crear conflictos de nombres entre los parámetros y los nombres de las columnas. Prefijo los nombres de mis parámetros con "_" en el ejemplo.

Su segunda función después de la edición no puede funcionar, porque solo devuelve parent mientras que el tipo de devolución se declara como SETOF stuff . Puede declarar cualquier tipo de devolución que desee, pero los valores de devolución reales deben coincidir con esa declaración. Es posible que desee utilizar RETURNS TABLE para eso.