Bagaimanakah Saya Boleh Melaksanakan Klausa ORDER BY dan LIMIT Berparameter dengan Cekap dalam Fungsi Jadual PostgreSQL?

PostgreSQL Susunan Berparameter Mengikut / Had dalam Fungsi Jadual

Menggunakan fungsi jadual dalam PostgreSQL membolehkan manipulasi dan perolehan data yang cekap. Walau bagaimanapun, apabila berurusan dengan set data yang besar, adalah penting untuk menggunakan pengisihan dan penomboran untuk mengendalikan hasil dengan berkesan.

Pertimbangkan senario berikut: kami mempunyai fungsi jadual, getStuff, yang melaksanakan pertanyaan SELECT ringkas pada jadual bernama barangan:

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

Pada mulanya, kami menggunakan fungsi sebagai berikut:

select * from getStuff('hello');

Walau bagaimanapun, untuk mengoptimumkan prestasi, kita perlu menggunakan klausa ORDER BY dan klausa LIMIT. Malangnya, melaksanakan pertanyaan seperti ini:

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

akan mendapatkan semula semua baris daripada jadual bahan dan kemudian memesan dan mengehadkannya, mengakibatkan pemprosesan yang tidak cekap.

Selain itu, PostgreSQL tidak menyediakan kaedah yang wujud untuk menghantar argumen ORDER BY kepada fungsi bahasa SQL, kerana hanya nilai boleh dipindahkan. Membina pertanyaan dalam fungsi plpgsql dan melaksanakannya melalui EXECUTE ialah pendekatan alternatif, tetapi ia bukanlah penyelesaian yang paling elegan.

Penyelesaian:

Mujurlah, fungsi plpgsql menawarkan penyelesaian yang berkesan untuk senario ini. Kami boleh mengubah suai fungsi getStuff untuk menerima parameter tambahan untuk ORDER BY dan LIMIT:

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying, _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;

Kini, kita boleh menggunakan fungsi dengan parameter yang dikehendaki:

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

RETURN QUERY EXECUTE penyata dengan cekap mendapatkan semula hasil pertanyaan dalam fungsi.

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