दिलचस्प पोस्ट
एक्सएसडी: शामिल और एक्सएसडी: आयात के बीच अंतर क्या है? सुरक्षित स्ट्रिंग टू बिगडीकल रूपांतरण ग्लोबल्स (), स्थानीय (), और वार्स () में अंतर क्या है? PyCharm में अनसुलझे संदर्भ मुद्दा क्या xml में सीडीएटीए एंड टोकन से बचने का एक तरीका है? क्या जावा फोकस (फ़ोकस) फ़ंक्शन पर <div> पर ध्यान केंद्रित करना संभव है? ज़ेरस के साथ एक स्ट्रिंग वाला बायां पैडिंग संचयी राशि कैसे प्राप्त करें जावास्क्रिप्ट का उपयोग करते हुए फ़ाइल (उपयोगकर्ता निर्देशिका) में कैसे लिखें? com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: संचार लिंक विफलता पायस्थन में हावरिसन फ़ॉर्मूला (दो जीपीएस बिंदुओं के बीच असर और दूरी) PHP DomDocument UTF-8 वर्ण (☆) को संभालने में असफल रहा है रूबी में सिस्टम () कॉल्स का आउटपुट प्राप्त करना निर्भर कैसे लोड करें और प्रदर्शित करें h: चयन करेंऑनमेनू को बदलने पर: चयन करेंमेनमेनू <H: ग्राफिक इमेज> या <img> टैग का उपयोग करके वेबएप / वेबकोनटेक्स्ट / परिनियोजन फ़ोल्डर से बाहर चित्र लोड करें

विभिन्न SELECT क्वेरीज़ के आउटपुट को वापस करने के लिए पीएल / पीजीएसयूएल फ़ंक्शन रिफैक्टर करें

मैंने एक ऐसा फ़ंक्शन लिखा था जो पाठ के रूप में अच्छी तरह से बना एक पोस्टग्रेश एसक्यूएल क्वेरी का उत्पादन करता है। अब मैं अब किसी पाठ को आउटपुट नहीं करना चाहता, लेकिन वास्तव में डेटाबेस के खिलाफ जनरेट किए गए SELECT कथन को चलाता है और परिणाम को वापस लौटाता हूं- जैसे क्वेरी स्वतः ही होता है

मैंने अभी तक क्या किया है:

 CREATE OR REPLACE FUNCTION data_of(integer) RETURNS text AS $BODY$ DECLARE sensors varchar(100); -- holds list of column names type varchar(100); -- holds name of table result text; -- holds SQL query -- declare more variables BEGIN -- do some crazy stuff result := 'SELECT\r\nDatahora,' || sensors || '\r\n\r\nFROM\r\n' || type || '\r\n\r\nWHERE\r\id=' || $1 ||'\r\n\r\nORDER BY Datahora;'; RETURN result; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION data_of(integer) OWNER TO postgres; 

sensors तालिका type लिए स्तंभ नामों की सूची रखता है वे समारोह के दौरान घोषित और भरे गए हैं। आखिरकार, वे ऐसे मान रखते हैं जैसे:

अंतर्निहित तालिकाओं की परिभाषा

वेरिएबल sensors इसी तालिका के type यहां प्रदर्शित सभी कॉलमों को बनाएंगे। उदाहरण के लिए: यदि type pcdmet तो sensors होगा 'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'

वेरिएबल का उपयोग एक SELECT कथन के निर्माण के लिए किया जाता है जो result में संग्रहीत result । पसंद:

 SELECT Datahora, column1, column2, column3 FROM myTable WHERE id=20 ORDER BY Datahora; 

अभी, मेरी फ़ंक्शन इस कथन को text रूप में वापस करता text । मैं इसे कॉपी-पेस्ट और pgAdmin या psql के माध्यम से निष्पादित करता हूं। मैं इसे स्वचालित करना चाहता हूं, स्वचालित रूप से क्वेरी को चलाने और परिणाम वापस लौटाता हूं। मैं उसे कैसे कर सकता हूँ?

वेब के समाधान से एकत्रित समाधान "विभिन्न SELECT क्वेरीज़ के आउटपुट को वापस करने के लिए पीएल / पीजीएसयूएल फ़ंक्शन रिफैक्टर करें"

गतिशील एसक्यूएल और RETURN प्रकार

(मैंने पिछले के लिए सर्वश्रेष्ठ को बचाया, पढ़ना जारी रखो!)
आप गतिशील SQL निष्पादित करना चाहते हैं प्रिंसिपल में, यह आसान है plpgsql में की मदद से EXECUTE आपको एक कर्सर की ज़रूरत नहीं है – वास्तव में, अधिकतर समय आप स्पष्ट कर्सर के बिना बेहतर होते हैं।
खोज के साथ SO पर उदाहरण पाएं

आप में जो समस्या आती है : आप अभी तक अनिर्धारित प्रकार के रिकॉर्ड वापस करना चाहते हैं। फ़ंक्शन को रिटर्न प्रकार के साथ RETURNS प्रकार घोषित करने की आवश्यकता है (या OUT या INOUT पैरामीटर के साथ) आपके मामले में आपको अनाम अभिलेखों में वापस गिरना होगा, क्योंकि लौटा कॉलम की संख्या , नाम और प्रकार अलग-अलग होते हैं। पसंद:

 CREATE FUNCTION data_of(integer) RETURNS SETOF record AS ... 

हालांकि, यह विशेष रूप से उपयोगी नहीं है इस तरह आपको फ़ंक्शन के प्रत्येक कॉल के साथ एक कॉलम परिभाषा सूची प्रदान करनी होगी। पसंद:

 SELECT * FROM data_of(17) AS foo ( colum_name1 integer ,colum_name2 text ,colum_name3 real); 

लेकिन आप यह भी कैसे करेंगे, जब आप कॉलम पहले से नहीं जानते हैं?
आप jsonb , hstore , hstore या xml जैसे कम संरचित दस्तावेज़ डेटा प्रकार का सहारा ले xml :

  • डेटाबेस में डेटा तालिका (या सूची <KeyValuePair <int, Object >>, or Dictionary) को कैसे संग्रहीत करें?

लेकिन इस प्रश्न के प्रयोजन के लिए मान लें कि आप व्यक्तिगत, सही ढंग से टाइप किए गए और नामांकित कॉलम जितना संभव हो उतना वापस लौटना चाहते हैं।

निश्चित रिटर्न प्रकार के साथ सरल समाधान

लगता है कि स्तंभ datahora दिया गया है, मैं डेटा टाइप timestamp ग्रहण करता हूं और हमेशा नाम और डेटा प्रकार के साथ दो और स्तंभ होते हैं।

नाम हम रिटर्न प्रकार में सामान्य नाम के पक्ष में छोड़ देंगे।
प्रकार हम भी छोड़ देंगे, और सभी डेटा प्रकार को text डाले जाने के बाद से सभी को text डालेंगे

 CREATE OR REPLACE FUNCTION data_of(_id integer) RETURNS TABLE (datahora timestamp, col2 text, col3 text) AS $func$ DECLARE _sensors text := 'col1::text, col2::text'; -- cast each col to text _type text := 'foo'; BEGIN RETURN QUERY EXECUTE ' SELECT datahora, ' || _sensors || ' FROM ' || quote_ident(_type) || ' WHERE id = $1 ORDER BY datahora' USING _id; END $func$ LANGUAGE plpgsql; 

यह कैसे काम करता है?

  • _sensors और _type इसके बजाय इनपुट पैरामीटर हो सकते हैं।

  • RETURNS TABLE खंड देखें

  • RETURN QUERY EXECUTE की प्रयोग को नोट करें RETURN QUERY EXECUTE यह गतिशील क्वेरी से पंक्तियों को वापस करने के और अधिक शानदार तरीकों में से एक है।

  • मैं फ़ंक्शन पैरामीटर के लिए एक नाम का उपयोग करता हूं, बस RETURN QUERY EXECUTE कम भ्रमित करने वाला है। एसक्यूएल-स्ट्रिंग में $1 फ़ंक्शन पैरामीटर का उल्लेख नहीं करता है, लेकिन USING क्लॉज के साथ पारित मूल्य के लिए। (दोनों इस सरल उदाहरण में अपने संबंधित क्षेत्र में $1 हो।)

  • _sensors लिए उदाहरण मान को नोट करें: प्रत्येक कॉलम को text टाइप करने के लिए डाली जाती है।

  • इस प्रकार का कोड एसक्यूएल इंजेक्शन के लिए बहुत कमजोर है। मैं इसके खिलाफ की रक्षा के लिए quote_ident() का उपयोग करता quote_ident() चर _sensors में कुछ कॉलम नामों को एक साथ _sensors को quote_ident() (और आमतौर पर एक बुरा विचार है!) का उपयोग करने से रोकता है। सुनिश्चित करें कि कोई भी बुरा चीज वहां पर हो सकती है, उदाहरण के लिए, अलग-अलग quote_ident() माध्यम से कॉलम नाम चलाने से। एक VARIADIC पैरामीटर दिमाग में आता है …

PostgreSQL 9.1+ के साथ सरलता

संस्करण 9.1 या बाद के साथ आप format() को सरल बनाने के लिए format() का उपयोग कर सकते हैं:

 RETURN QUERY EXECUTE format(' SELECT datahora, %s -- identifier passed as unescaped string FROM %I -- assuming the name is provided by user WHERE id = $1 ORDER BY datahora' ,_sensors, _type) USING _id; 

फिर, अलग-अलग कॉलम के नाम ठीक से भाग जाएंगे और साफ तरीके से हो सकते हैं।

वही प्रकार साझा करने वाले कॉलम की संख्या

आपके प्रश्न के अपडेट के बाद यह दिखता है कि आपके रिटर्न का प्रकार है

  • कॉलम की एक चर संख्या
  • लेकिन एक ही प्रकार के सभी कॉलम double precision (उर्फ float8 )

जैसा कि हमें किसी फ़ंक्शन के RETURN प्रकार को परिभाषित करना पड़ता है, मैं इस स्थिति में RETURN प्रकार का प्रयोग करता हूं, जो कि मानों की एक चर संख्या रख सकता है। इसके अतिरिक्त, मैं स्तंभ नामों के साथ एक सरणी वापस करता हूं, ताकि आप परिणाम के नामों को भी पार्स कर सकें:

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

विभिन्न पूर्ण तालिका प्रकार

यदि आप वास्तव में एक तालिका के सभी स्तंभों को वापस करने की कोशिश कर रहे हैं (उदाहरण के लिए लिंक किए गए पृष्ठ पर तालिकाओं में से एक , फिर एक बहुरूपिक प्रकार के साथ इस सरल, बहुत शक्तिशाली समाधान का उपयोग करें:

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

कॉल करें:

 SELECT * FROM data_of(NULL::pcdmet, 17); 

किसी अन्य तालिका नाम के साथ कॉल में pcdmet को बदलें।

यह कैसे काम करता है?

  • anyelement एक छद्म डेटा प्रकार है, एक anyelement प्रकार, किसी भी गैर-सरणी डेटा प्रकार के लिए प्लेसहोल्डर। फ़ंक्शन में किसी भी प्रकार के किसी भी प्रकार के चलने के समय में दिए गए समान प्रकार का मूल्यांकन करें। फ़ंक्शन के तर्क के रूप में एक परिभाषित प्रकार के मान की आपूर्ति करके, हम निहित रूप से वापसी प्रकार को परिभाषित करते हैं।

  • PostgreSQL स्वतः प्रत्येक तालिका के लिए एक पंक्ति प्रकार (एक समग्र डेटा प्रकार) को परिभाषित करता है, इसलिए प्रत्येक तालिका के लिए एक अच्छी तरह परिभाषित प्रकार है इसमें अस्थायी टेबल शामिल हैं, जो तदर्थ उपयोग के लिए सुविधाजनक है।

  • किसी भी प्रकार की NULL हो सकती NULL इसलिए हम एक NULL मूल्य में हाथ NULL , टेबल प्रकार पर डालें।

  • अब फ़ंक्शन एक अच्छी तरह से परिभाषित पंक्ति प्रकार लौटाता है और हम SELECT * FROM data_of(...) का उपयोग करके पंक्ति को सड़ कर और अलग-अलग कॉलम प्राप्त कर सकते हैं।

  • pg_typeof(_tbl_type) वस्तु पहचानकर्ता प्रकार regtype रूप में तालिका का नाम देता है। स्वचालित रूप से text परिवर्तित होने पर, आइडेंटिफ़ार्स स्वचालित रूप से डबल-उद्धृत और स्कीमा-योग्य हैं यदि आवश्यक हो इसलिए, एसक्यूएल इंजेक्शन एक संभव नहीं है। यह स्कीमा योग्य तालिका-नामों से भी निपट सकता है जहां quote_ident() विफल हो जाएगा ।

आप शायद एक कर्सर वापस करना चाहते हैं इस तरह से कुछ प्रयास करें (मैंने इसे करने की कोशिश नहीं की है):

 CREATE OR REPLACE FUNCTION data_of(integer) RETURNS refcursor AS $BODY$ DECLARE --Declaring variables ref refcursor; BEGIN -- make sure `sensors`, `type`, $1 variable has valid value OPEN ref FOR 'SELECT Datahora,' || sensors || ' FROM ' || type || ' WHERE nomepcd=' || $1 ||' ORDER BY Datahora;'; RETURN ref; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION data_of(integer) OWNER TO postgres; 

मुझे कहने के लिए क्षमा करें, लेकिन आपका सवाल बहुत स्पष्ट नहीं है। हालांकि नीचे आप एक स्व पाएंगे उदाहरण के लिए कैसे एक कर्सर चर को वापस करने वाला फ़ंक्शन बना और उपयोग करें आशा करता हूँ की ये काम करेगा !

 begin; create table test (id serial, data1 text, data2 text); insert into test(data1, data2) values('one', 'un'); insert into test(data1, data2) values('two', 'deux'); insert into test(data1, data2) values('three', 'trois'); create function generate_query(query_name refcursor, columns text[]) returns refcursor as $$ begin open query_name for execute 'select id, ' || array_to_string(columns, ',') || ' from test order by id'; return query_name; end; $$ language plpgsql; select generate_query('english', array['data1']); fetch all in english; select generate_query('french', array['data2']); fetch all in french; move absolute 0 from french; -- do it again ! fetch all in french; select generate_query('all_langs', array['data1','data2']); fetch all in all_langs; -- this will raise in runtime as there is no data3 column in the test table select generate_query('broken', array['data3']); rollback;