दिए गए तालिका और स्तंभ नामों के लिए तालिका लौटाने का कार्य

SQL एक कड़ाई से टाइप की जाने वाली भाषा है और Postgres फंक्शन को अपना रिटर्न टाइप घोषित करना चाहिए। किसी फ़ंक्शन से स्तंभों की एक चर संख्या लौटाना केवल बहुरूपी प्रकारों जैसे वर्कअराउंड के साथ ही संभव है। देखो:

• पीएल/पीजीएसक्यूएल में रोटाइप द्वारा टेबल कैसे वापस करें?

लेकिन हम आपके मामले में पंक्ति प्रकार के साथ काम नहीं कर सकते, क्योंकि यह डेटाबेस से डेटाबेस में भिन्न होता है। शेष विकल्प: गुमनाम रिकॉर्ड लौटाएं और प्रत्येक कॉल के साथ एक कॉलम परिभाषा सूची प्रदान करें। मैं आमतौर पर इसकी अनुशंसा नहीं करता, क्योंकि प्रत्येक कॉल के साथ कॉलम परिभाषा सूची प्रदान करना कठिन हो सकता है - और अक्सर व्यर्थ। लेकिन आपका दुर्लभ उपयोग के मामलों में से एक हो सकता है जहां यह समझ में आता है।

फिर भी, आपको संभावित रूप से अनुपलब्ध स्तंभों का डेटा प्रकार जानना होगा। मैं इस डेमो के उद्देश्य के लिए integer मान लूंगा। अन्यथा आपको डेटा प्रकारों को अतिरिक्त रूप से पास करना होगा और तदनुसार क्वेरी बनाना होगा।

CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl regclass
                                          , _cols VARIADIC text[])  -- ①
  RETURNS SETOF record     -- ② anonymous records
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE    -- ③ dynamic SQL
   format(
      'SELECT %s FROM %s'  -- ④ safe against SQLi
    , (
      SELECT string_agg(COALESCE(quote_ident(a.attname)
                              , '0 AS ' || quote_ident(t.col)  -- assuming integer!
                                ), ', ' ORDER  BY t.ord) -- ⑤
      FROM   unnest(_cols) WITH ORDINALITY t(col, ord)   -- ⑤
      LEFT   JOIN pg_attribute a ON a.attrelid = _tbl    -- ⑥
                                AND a.attnum > 0
                                AND NOT a.attisdropped 
                                AND a.attname = t.col
      )
    , _tbl
   );
END
$func$;

कॉल करें (महत्वपूर्ण!)

SELECT *
FROM   f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int); -- ② column definition list

आपका उदाहरण कॉल, इन स्तंभों पर आधारित भावों के साथ:

SELECT pool, case when spa = 1 then 1 else 0 end as has_spa  -- ⑦ expressions
     , sauna, house_size
     , case when no_rooms > 2 then 1 else 0 end as rooms 
FROM f_dynamic_select('static', 'pool', 'spa', 'sauna', 'house_size', 'no_rooms')
AS t(pool int, spa int, house_size int, sauna int, no_rooms int);

db<>fiddle यहां

फ़ंक्शन एक टेबल नाम को regclass प्रकार के रूप में लेता है। देखो:

• PostgreSQL फ़ंक्शन पैरामीटर के रूप में तालिका का नाम

... इसके बाद कॉलम नामों की एक मनमाना सूची - अर्थपूर्ण क्रम में। VARIADIC इसके लिए सुविधाजनक होना चाहिए। देखो:

• एकल पैरामीटर में एकाधिक मान पास करें

ध्यान दें कि हम कॉलम नामों को केस-संवेदी, एकल-उद्धृत स्ट्रिंग्स के रूप में पास करते हैं। नहीं (दोहरे-उद्धृत) पहचानकर्ता।

यह पहली बार हो सकता है जब मैं किसी फ़ंक्शन से अनाम रिकॉर्ड वापस करने की अनुशंसा करता हूं - [plpgsql] टैग पर करीब 1000 उत्तरों के बाद। मैनुअल:

यदि फ़ंक्शन को record डेटा प्रकार लौटाने के रूप में परिभाषित किया गया है, फिर एक उपनाम या कीवर्ड AS मौजूद होना चाहिए, उसके बाद एक कॉलम होना चाहिए ( column_name data_type [, ... ]) के रूप में परिभाषा सूची। NS कॉलम परिभाषा सूची को वास्तविक संख्या और प्रकार से मेल खाना चाहिए फ़ंक्शन द्वारा लौटाए गए कॉलम।

③ डायनेमिक SQL पर मैनुअल।

④ SQL इंजेक्शन से सुरक्षित है, क्योंकि तालिका का नाम regclass के रूप में पास किया गया है, और SELECT सूची को quote_ident() का सावधानीपूर्वक उपयोग करके संयोजित किया गया है। देखो:

• plpgsql फ़ंक्शन में तालिका और स्तंभ नामों को तर्क के रूप में परिभाषित करें?

स्तंभों के मूल क्रम को संरक्षित करने के लिए WITH ORDINALITY का प्रयोग करें। देखो:

• पोस्टग्रेएसक्यूएल अननेस्ट () तत्व संख्या के साथ

⑥ LEFT JOIN से सिस्टम कैटलॉग pg_attribute< /a> मौजूदा कॉलम की पहचान करने के लिए। देखो:

• PostgreSQL में विशेष कॉलम नामों वाले कॉलम चुनें

एक्सप्रेशन बिल्डिंग को पास किए गए कॉलम पर बाहरी SELECT पर ले जाएं।

अस्वीकरण: मैं इस स्तर के परिष्कार का परिचय तभी दूंगा जब मेरे पास होने के लिए होगा। हो सकता है कि आप प्रत्येक डेटाबेस में साधारण विचारों के साथ काम कर सकें?