Bagaimanakah Saya Boleh Mengubah Suai Fungsi PL/pgSQL untuk Mengembalikan Keputusan Pertanyaan SQL Dinamik?

Ubah suai fungsi PL/pgSQL untuk mengembalikan hasil pertanyaan

Latar belakang

Pada mulanya, anda mencipta fungsi yang menghasilkan rentetan pertanyaan PostgreSQL SELECT yang dibentuk dengan baik. Sekarang anda ingin melaksanakan pernyataan SELECT yang dijana secara langsung terhadap pangkalan data dan mendapatkan semula keputusannya.

Kaedah: SQL Dinamik dan Jenis Pulangan

SQL Dinamik

Untuk melaksanakan SQL dinamik, kami menggunakan perintah EXECUTE. Memandangkan anda tidak menggunakan kursor, anda boleh menggunakan sintaks RETURN QUERY EXECUTE.

Jenis Pemulangan

Cabarannya ialah mengembalikan rekod jenis yang tidak ditentukan kerana fungsi perlu mengisytiharkan jenis pulangan. Kami akan mempertimbangkan pelbagai pendekatan bergantung pada ciri khusus data.

Penyelesaian mudah menggunakan jenis pulangan tetap

Dengan mengandaikan jenis pulangan tetap untuk cap masa, teks dan lajur teks, kami boleh mentakrifkan fungsi seperti berikut:

CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora, %s
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _id;
END
$$ LANGUAGE plpgsql;

Bilangan lajur boleh ubah daripada jenis yang sama

Jika anda mempunyai bilangan lajur yang berubah-ubah, dan semua lajur adalah daripada jenis yang sama (cth. berganda), kita boleh menggunakan jenis ARRAY untuk menyarangkan nilai:

CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora
           , string_to_array(, ',')  -- AS names
           , ARRAY[%s]            -- AS values
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _sensors, _id;
END
$$ LANGUAGE plpgsql;

Pelbagai jenis meja lengkap

Untuk mengembalikan semua lajur jadual, kita boleh menggunakan jenis polimorfik:

CREATE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement
AS $$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT *
       FROM   %I  -- pg_typeof returns regtype, quoted automatically
       WHERE  id = 
       ORDER  BY datahora',
      pg_typeof(_tbl_type)
   )
   USING _id;
END
$$ LANGUAGE plpgsql;

Nota: Dalam contoh kedua, fungsi string_to_array memerlukan pembatas dan , ditambahkan di sini sebagai pembatas. Contoh ketiga menggunakan pengecam pemformatan %I untuk mengendalikan nama jadual dengan cara yang lebih selamat untuk mengelakkan suntikan SQL. Kaedah yang anda pilih bergantung pada keperluan khusus anda dan struktur data. Jika boleh, memberikan lebih banyak maklumat tentang struktur jadual anda dan hasil yang diingini boleh membantu saya menyediakan penyelesaian yang lebih tepat dan cekap.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Mengubah Suai Fungsi PL/pgSQL untuk Mengembalikan Keputusan Pertanyaan SQL Dinamik?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!