Bagaimanakah Saya Boleh Memfaktorkan Semula Fungsi PL/pgSQL untuk Mengembalikan Hasil daripada Pertanyaan SELECT Dinamik dengan Nombor dan Jenis Lajur Pembolehubah?

Refactor PL/pgSQL berfungsi untuk mengembalikan output pelbagai pertanyaan PILIH

Pengenalan

Anda mempunyai fungsi PL/pgSQL yang menjana pertanyaan SELECT sebagai rentetan teks. Sekarang anda ingin mempertingkatkan lagi fungsi ini untuk melaksanakan pertanyaan yang dijana dan mengembalikan hasil sebenar, serupa dengan tingkah laku pertanyaan kendiri.

Jenis pulangan boleh ubah

Cabarannya ialah struktur dan jenis data yang dikembalikan mungkin berbeza-beza bergantung pada jadual asas yang disoal. Penyelesaian mudah adalah untuk menentukan jenis pulangan tetap dengan nama lajur biasa dan menghantar semua nilai ke teks, seperti dalam fungsi yang diubah suai seperti ini:

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = 
      ORDER  BY datahora'
   USING  _id;
END
$func$;</code>

Mengendalikan bilangan pembolehubah lajur

Jika bilangan lajur yang dikembalikan berubah, tetapi jenisnya kekal sama (mis. semuanya float8), anda boleh menggunakan tatasusunan nilai bersarang:

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[])
  LANGUAGE plpgsql AS
$func$
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
$func$;</code>

Kembalikan mana-mana jenis jadual secara dinamik

Untuk mengendalikan pelbagai jenis jadual lengkap dengan struktur berbeza, anda boleh memanfaatkan jenis polimorfik:

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

Fungsi ini memerlukan anda menghantar nilai NULL yang dihantar ke jenis jadual sasaran (contohnya, NULL::pcdmet). Ia kemudian secara automatik menentukan jenis baris yang sesuai dan mengembalikan lajur individu apabila digunakan dengan SELECT * FROM data_of().

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Memfaktorkan Semula Fungsi PL/pgSQL untuk Mengembalikan Hasil daripada Pertanyaan SELECT Dinamik dengan Nombor dan Jenis Lajur Pembolehubah?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!