मान लें कि मेरे पास दो अलग-अलग डेटाबेस houses और apartments में static नाम की एक टेबल है।

static तालिका में house_size, no_rooms, pool, spa जैसे घरों की स्थिर जानकारी है।

static तालिका में houses डेटाबेस में इस तरह के कॉलम हैं:

pool    spa house_size  sauna   no_rooms
   1    1         25    1       2
   1    0         35    1       3

static तालिका में apartments डेटाबेस में इस तरह के कॉलम हैं:

pool    spa house_size  sauna   
   1    1         25    1       
   1    0         35    1       

मैं बिना किसी त्रुटि के क्वेरी के नीचे चलाना चाहता हूं। वर्तमान में, मुझे त्रुटि मिलती है क्योंकि no_rooms कॉलम apartments.public.static में मौजूद नहीं है।

select pool, case when spa = 1 then 1 else 0 end as has_spa,
      sauna, house_size, case when no_rooms > 2 then 1 else 0 end as rooms 
from static;

समाधान मैंने कोशिश की:

WITH static_new AS (SELECT s.*     
FROM (SELECT 0 AS no_rooms) AS dummy 
LEFT JOIN LATERAL
( SELECT
      pool, spa, sauna, house_size, no_rooms
  FROM static
)  AS s on true)
SELECT * FROM static_new;

यह काम करता है, लेकिन अधिक कॉलम शामिल होने पर यह क्वेरी गड़बड़ हो जाती है।

मैं क्या ढूंढ रहा हूं:

  1. एक फ़ंक्शन बनाएं जो कॉलम नाम और तालिका का नाम लेता है और फिर वह शामिल होता है जो मैं उपरोक्त क्वेरी में कर रहा हूं और एक तालिका देता है। (सामान्य होना चाहिए और पैरामीटर में दिए गए कॉलम नाम और तालिका नाम के लिए काम करना चाहिए और एक तालिका वापस करना चाहिए।)

  2. कोई अन्य अच्छा और साफ समाधान?

1
Awaish Kumar 1 जुलाई 2020, 17:00

2 जवाब

सबसे बढ़िया उत्तर

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 प्रकार के रूप में लेता है। देखो:

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

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

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

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

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

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

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

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

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


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

2
Erwin Brandstetter 2 जुलाई 2020, 16:35

प्रश्नों के साथ मत उलझो। सभी स्तंभों के साथ दोनों डेटाबेस में एक दृश्य जोड़ें। पहले डेटाबेस में:

create view v_static as
    select pool, spa house_size, sauna, no_rooms
    from status;

क्षण में:

create view v_static as
    select pool, spa house_size, sauna, null as no_rooms
    from status;

फिर बेस टेबल के बजाय व्यू का इस्तेमाल करें।

0
Gordon Linoff 1 जुलाई 2020, 17:04