PostgreSQL con parametri Ordine / Limite in funzione della tabella

Ho una funzione sql che fa una semplice istruzione sql select:

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

Per ora sto richiamando questa funzione come questa:

select * from getStuff('hello');

Quali sono le mie opzioni, se ho bisogno di ordine e limitare i risultati con order by e limit clausole?

Credo che una query come questa:

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

non essere molto efficiente, perché tutte le righe dalla tabella stuff sarà restituito dalla funzione getStuff e solo allora ordinato e tagliata dal limite.

Ma anche se io sono di destra, non c’è nessun modo facile per passare l’ordine per argomento di un linguaggio sql funzione. Solo i valori possono essere passati, e non parti di istruzione sql.

Un’altra opzione è quella di creare la funzione in plpgsql lingua, dove è possibile costruire la query ed eseguire tramite EXECUTE. Ma questo non è molto bello, con un approccio sia.

Così, c’è qualche altro metodo per ottenere questo?
O quale opzione scegliere? Ordinazione/limitando al di fuori della funzione, o plpgsql?

Sto usando postgresql 9.1.

Modifica

Ho modificato la FUNZIONE CREA dichiarazione come questa:

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;


Questo genera:


ERRORE: il CASO di tipi di carattere variabili intere e non può essere eguagliata
ŘÁDKA 13: QUANDO $1 = ‘parent’, ALLORA t.padre

Il stuff tabella simile a questa:

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

Ho male leggere Dems codice. Ho corretto la domanda. Questo codice funziona per me.

  • Perché utilizzo di PL/pgSQL e EXECUTE non è un bel approccio? Non dovrebbe fare una grande differenza in termini di prestazioni ed è l’unica soluzione che mi viene in mente.
  • Hmm, principalmente a causa della prestazione che ho pensato che sarebbe molto bassa in comparsion con linguaggio sql funzione, o almeno paragonabili a select * from getStuff('hello') order by col2 limit 100; che è più bello di scrivere a me (dal punto di vista di tutta l’app che sto costruendo)
  • utilizzando EXECUTE sarà un po ‘ più lento (causa di ulteriori analisi in corso), ma dubito che sarete in grado di misurare la differenza.
  • Indovinare impatto sulle performance, di solito non funzionano bene.
  • Ok: -), sentitevi liberi di scrivere una risposta e l’accetterò, se nessuno sarà altra soluzione 🙂
  • naturalmente, lo so. Dovrei fare qualche misurazione. Ma in questo caso, sono abbastanza sicuro che plpgsql funzioni sono di grado più lento rispetto a sql in modo che non è venuto in mente di misurare qualcosa.
  • ESEGUIRE ha un overhead dell’analisi e della compilazione, ma se postgresql permette di parametrizzare sql dinamico, questo può essere cacheable e così, semplicemente, da un hash di ricerca di sovraccarico. Tali spese non sono molto evidenti, tranne in molto rapidamente la ripetizione di query. Si noti, inoltre, che le alternative sono diverse in testa un one size fits all piano. E un tale progetto può essere così straordinariamente efficiente che paralizza le prestazioni.
  • Quindi, se ho capito bene, si consiglia di utilizzare, piuttosto EXECUTE di ORDER BY CASE?
  • Dovresti test per casi particolari. Quantità di dati, ammissibile di combinazioni di campi di ricerca, indici disponibili, la frammentazione dei dati, ecc, possono avere un impatto. SQL dinamico con ESECUZIONE sarà sempre resa un piano che esegue pari o meglio di un singolo CASO in base espressione. Ma si sente messier e quindi può essere più difficile da mantenere. Le prove mostrano le differenze di prestazioni. Mi capita spesso di valore di manutenzione su di prestazioni se le differenze di prestazioni non sono contrassegnati.
  • Vi ringrazio molto. I vostri commenti sono molto utili!

InformationsquelleAutor JoshuaBoshi | 2011-11-15



4 Replies
  1. 26

    C’è niente di sbagliato con un plpgsql funzione. È il più elegante e il più veloce soluzione per qualcosa di un po ‘ più complesso. L’unica situazione in cui le prestazioni possono soffrire è quando un plpgsql funzione nidificata, perché la query planner non può ottimizzare ulteriormente i contenuti del codice nel contesto della query esterna che può o non può rendere più lento.

    Ulteriori dettagli in questa ultima risposta:

    In questo caso è molto più semplice rispetto a un sacco di CASE clausole in una query:

    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;

    Chiamata:

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

    Note

    • Utilizzare RITORNO ESEGUI per restituire i risultati della query in un colpo solo.
    • Utilizzare quote_ident() per gli identificatori di salvaguardia contro SQLi.
      O format() per qualcosa di più complesso. Related:

    • Mano i valori dei parametri con il USO clausola per evitare di casting, citando e SQLi, ancora una volta.
    • Essere attenti a non creare conflitti tra i parametri e i nomi di colonna. Ho preceduto nomi di parametro con un carattere di sottolineatura (_) nell’esempio. Solo una preferenza personale.

    Seconda funzione dopo la modifica non può funzionare, perché solo ritorno parent mentre il tipo di ritorno è dichiarato SETOF stuff. È possibile dichiarare qualsiasi tipo di ritorno che ti piace, ma effettivi valori di ritorno coincide con la dichiarazione. Si potrebbe desiderare di utilizzare RESTITUISCE la TABELLA per che.

    • Brandstetter: Wow, ora che hai imparato alcuni mi plpgsql 🙂 non ho avuto idea di RETURN QUERY EXECUTE e USING. Questa è davvero la soluzione elegante, e non ho nessuna preoccupazione per plpgsql metodo ora. Vi ringrazio molto!
    • come posso aggiungere ASC o DESC con esso?
    • Basta aggiungere. Vedere il mio aggiornamento.
    • Voglio farlo dinamico come si è fatto per _orderby. Voglio passare crescente o decrescente in modo dinamico.
    • Fai una nuova domanda con i relativi dettagli. I commenti non sono del posto. È sempre possibile collegare a questo se avete bisogno di un contesto. Ci sono semplici & soluzioni sicure.
    • Si prega di controllare questa domanda. stackoverflow.com/questions/49775242/…

  2. 2

    Se la funzione è stabile (non modificare il database), la query planner in genere inline di esso. Pertanto, facendo SELECT * FROM getStuff('x') LIMIT 10 produrrà lo stesso piano di query come se il limite fosse all’interno di getStuff().

    Tuttavia, è necessario dire a PG la tua funzione è stabile dichiarando come tali:

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

    Ora fa EXPLAIN SELECT * FROM getStuff('x') LIMIT 1 dovrebbe produrre lo stesso piano di query come scrivere l’equivalente query.

    L’inlining dovrebbe funzionare anche per ORDER BY clausole al di fuori della funzione. Ma se si voleva parametrizzare la funzione di determinare l’ordine, si potrebbe fare così anche per controllare la direzione di ordinamento:

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

    Purché sort_col e sort_dir sono costanti all’interno della query, la query planner dovrebbe essere in grado di semplificare il verbose alla ricerca di query per

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

    che è possibile verificare utilizzando EXPLAIN.

  3. 1

    Per il ORDER BY si può provare qualcosa di simile a questo:

    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

    Si potrebbe fare un po ‘ di conversioni di tipi di dati, in modo che tutti i tipi di dati in CASE risultato della partita. Basta essere attenti circa la conversione di numeri in stringhe – dovrai anteporre 0s per rendere il loro ordine corretto. Lo stesso vale per i valori di data/ora. Con un formato che ha anno poi il mese poi il giorno, etc.

    Questo l’ho fatto io in SQL Server, ma mai in PostgreSQL, e non ho una copia di PostgreSQL su questa macchina, così non è stato testato.

    • Per evitare il tipo di dati problema… ORDER BY CASE WHEN $2 = 'a' THEN a END, CASE WHEN $2 = 'b' THEN b END, etc, etc. Ma, attenzione, questo ha lo stesso problema di ottimizzazione come ho accennato in soulcheck risposta.
    • Non è questo il tipo di dati problema che mi riferivo. Obvisously $2 sarà sempre lo stesso tipo di dati. Se la colonna a e la colonna b sono diversi tipi di dati, anche se poi potrebbe causare alcuni problemi.
    • L’esempio che ho dato si occupa da ogni settore separato della clausola ORDER BY. Il CASO di dichiarazioni resa ORDER BY null, null, x, null (per esempio), e, di conseguenza, il tipo di dati di indipendenza.
    • Grazie per le vostre risposte e commenti. Sto usando Dems versione di CASE ma postgresql tiri: ERROR: CASE types character varying and integer cannot be matched quando cerco di creare la funzione (in posizione dopo THEN – strano). Vorrei modificare la domanda e aggiungi sorgente completo…
    • Esattamente il problema di cui ho parlato. È necessario assicurarsi che è possibile convertire tutti i risultati per lo stesso tipo di dati.
    • Non postgres richiedono l’istruzione ELSE? ORDER BY (CASE WHEN $2 = 'a' THEN table.a ELSE NULL END), (CASE WHEN $2 = 'b' THEN table.b ELSE NULL END), etc, etc?
    • Scusate, ho letto i democratici codice di male, e io non fare CASO a parte, per ogni “ramo”. Ora si sta lavorando e si rende il senso 🙂
    • Mi dispiace, vedi il mio precedente commento qui .-X

  4. 0

    Si può superare il limite di valore come argomento di funzione senza problemi. Come per l’ordinazione è possibile utilizzare ODER in combinazione con l’istruzione CASE. Questo, purtroppo, non lavoro per qualcosa come

    ORDER BY CASE condition_variable
    WHEN 'asc' THEN column_name ASC
    ELSE column_name DESC
    END;
    • 1) è necessario sostituire 1 con a. 2) Questo consentirà di evitare l’ottimizzatore di essere in grado di utilizzare gli indici, etc. È non rendimento di una singola query per diversi scopi, ma questo contro di sql dinamico di controllare ciò che le prestazioni sovraccarico. (Un solo piano possono essere creati per ogni singola query, ma diverse clausole order by maggio richiedono diversi piani per essere efficace.)
    • E ‘ corretto, ordini, prima colonna crescente o decrescente a seconda se a è uguale a ‘asc’ o non. ma io a modificare in ogni caso, per renderlo più chiaro.
    • Questo non è legale. Non si può avere ASC o DESC all’interno dell’espressione.
    • sì, è stato un esempio di cosa non funziona 😉 Molto vecchia risposta in modo che la qualità lascia molto a desiderare.

Lascia un commento