Bagaimana untuk Melaksanakan ORDER BY dan LIMIT Berparameter dengan Cekap dalam Fungsi Jadual PostgreSQL?

Tertib Berparameter Mengikut dan Had dalam Fungsi Jadual PostgreSQL

Fungsi SQL tersuai, dinamakan getStuff, telah dicipta untuk melaksanakan pernyataan SELECT asas. Apabila menggunakan fungsi dalam bentuk pilih * daripada getStuff('hello'), ia mengambil semua baris daripada jadual bahan berdasarkan nilai parameter yang disediakan.

Walau bagaimanapun, persoalan timbul: bagaimana kita boleh melaksanakan pesanan dan mengehadkan dalam fungsi ini untuk meningkatkan kecekapan dan mendapatkan hanya baris yang dikehendaki? Menggunakan susunan mengikut dan menghadkan klausa secara terus dalam fungsi getStuff akan menjadi tidak cekap kerana ia melibatkan pengambilan semua baris sebelum menggunakan penapis.

Penyelesaian yang disyorkan ialah menggunakan PL/pgSQL, bahasa prosedur dalam PostgreSQL, untuk mencipta fungsi. PL/pgSQL membolehkan pembinaan dan pelaksanaan pertanyaan SQL dinamik, memberikan fleksibiliti dalam mengendalikan nilai parameter.

Fungsi contoh dalam PL/pgSQL:

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 = 
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  '
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

Fungsi ini mengambil tiga parameter: _param (nilai untuk ditapis mengikut), _orderby (lajur untuk diisih mengikut), dan _had (bilangan maksimum baris untuk kembali).

Untuk memanggil fungsi:

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

Dengan menggunakan RETURN QUERY EXECUTE, hasil pertanyaan SQL yang dibina dikembalikan dalam satu kelompok. quote_ident melindungi terhadap suntikan SQL dengan melarikan diri dari pengecam. Parameter dihantar melalui USING untuk mengelakkan suntikan SQL dan kemungkinan isu penghantaran/petik.

Untuk pertanyaan yang lebih kompleks, pertimbangkan untuk menggunakan fungsi format().

Walaupun fungsi SQL mungkin sesuai untuk operasi mudah , PL/pgSQL menawarkan fleksibiliti dan keupayaan pengoptimuman yang lebih besar apabila mengendalikan klausa ORDER BY dan LIMIT berparameter dalam jadual fungsi.

Atas ialah kandungan terperinci Bagaimana untuk Melaksanakan ORDER BY dan LIMIT Berparameter dengan Cekap dalam Fungsi Jadual PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!