From: | Shubhra Sharma <sharma(dot)shubhra07(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Need help. Getting an error in my function when I do a select * from foo_bar() |
Date: | 2014-06-11 16:08:33 |
Message-ID: | CABFOHTxvs5t1rJF7vdV=vsChFh7iXBtu-hFC-kZsgvPsm34_Vw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
-- Function: foo_bar()
-- DROP FUNCTION foo_bar();
CREATE OR REPLACE FUNCTION foo_bar()
RETURNS SETOF foo_bar_type AS
$BODY$
DECLARE
sys_id bigint default 1;
db_name text;
outer_query text;
per_inventory_query text;
counter integer default 0;
BEGIN
select count(1) as counter into counter from inventory_system;
for sys_id IN select distinct system_id from inventory_system_properties
Loop
db_name:= concat('inventory',sys_id);
per_inventory_query:= quote_literal((select
A.company_name, A.id as system_id,A.name as system_name,B.* from
fetch_cucm_systems() as A
cross join
(
SELECT t1.devicepkid,
t1.devicepoolpkid,
t1."devicePoolName",
t1."primaryCallManager",
t1."activeCallManager",
t1."callManagerGroup",
t1."directoryNumberName",
t1.shared_flag,
t1."deviceName",
t1."webInfoExtracted",
t1."deviceDescription",
t1."modelName",
t1."deviceProtocol",
t1."deviceCSS",
t1."dnCSS",
t1."aarCSS",
t1."aarGroup",
t1."mediaResourceGroupList",
t1."userMohAudioSource",
t1."networkMohAudioSource",
t1.location,
t1."userLocale",
t1."networkLocale",
t1."deviceSecurityMode",
t1."extensionMobility",
t1.logintime,
t1."phoneUserName",
t1.ctienabled,
t1."phoneTemplate",
t1."softkeyTemplate",
t1."modelNumber",
t1."retryVideoCallAsAudio",
t1."disableSpeaker",
t1."disableSpeakerAndHeadset",
t1."forwardingDelay",
t1."pcPort",
t1."settingsAccess",
t1.garp,
t1."voiceVlanAccess",
t1."videoCapability",
t1."autoSelectLineEnable",
t1."webAccess",
t1."lastRegistrationDate",
t1."registrationState",
t1."dateDiscovered",
t1."ipAddress",
t1."pcPortConfiguration",
t1."accessPortInformation",
t1."swPortConfiguration",
t1."networkPortInformation",
t1."neighborDeviceId",
t1."neighborIpAddress",
t1."neighborPort",
t1."subnetMask",
t1."networkSegment",
t1."dhcpEnabled",
t1."dhcpServer",
t1.tftpserver1,
t1.tftpserver2,
t1.alttftpserver,
t1.securitymode,
t1."defaultRouter1",
t1."domainName",
t1."dnsServer1",
t1."dnsServer2",
t1.phoneload,
t1."appLoadId",
t1.defaultsccpload,
t1.defaultsipload,
t1."bootLoadId",
t1.version,
t1."serialNumber",
t1."hardwareRevision",
t1."numberOfAddOnModules",
t1."operationalVlanId",
t1."adminVlanId",
t1.amps,
t1."e911Location",
t1."messageWaiting",
t1."expansionModule1",
t1."expansionModule2",
t1."spanToPCPort",
t1."pcVlan",
t1."messagesUrl",
t1."authenticationUrl",
t1."proxyServerUrl",
t1."idleUrl",
t1."servicesUrl",
t1."directoriesUrl",
t1."informationUrl",
t1."loginUserId"
FROM dblink ('dbname = '|| db_name ||'::text user=blah
password=blah123'::text, 'select * from v_detailed_phone_inventory'::text )
t1(devicepkid text, devicepoolpkid text, "devicePoolName" text,
"primaryCallManager" text, "activeCallManager" text,
"callManagerGroup" text, "directoryNumberName" text, shared_flag text,
"deviceName" text, "webInfoExtracted" text, "deviceDescription" text,
"modelName" text, "deviceProtocol" text, "deviceCSS" text, "dnCSS" text,
"aarCSS" text, "aarGroup" text, "mediaResourceGroupList" text,
"userMohAudioSource" text, "networkMohAudioSource" text, location text,
"userLocale" text, "networkLocale" text, "deviceSecurityMode" text,
"extensionMobility" text, logintime text, "phoneUserName" text, ctienabled
text, "phoneTemplate" text, "softkeyTemplate" text, "modelNumber" text,
"retryVideoCallAsAudio" text, "disableSpeaker" text,
"disableSpeakerAndHeadset" text, "forwardingDelay" text, "pcPort" text,
"settingsAccess" text, garp text, "voiceVlanAccess" text, "videoCapability"
text, "autoSelectLineEnable" text, "webAccess" text, "lastRegistrationDate"
text, "registrationState" text, "dateDiscovered" text, "ipAddress" text,
"pcPortConfiguration" text, "accessPortInformation" text,
"swPortConfiguration" text, "networkPortInformation" text,
"neighborDeviceId" text, "neighborIpAddress" text, "neighborPort" text,
"subnetMask" text, "networkSegment" text, "dhcpEnabled" text, "dhcpServer"
text, tftpserver1 text, tftpserver2 text, alttftpserver text, securitymode
text, "defaultRouter1" text, "domainName" text, "dnsServer1" text,
"dnsServer2" text, phoneload text, "appLoadId" text, defaultsccpload text,
defaultsipload text, "bootLoadId" text, version text, "serialNumber" text,
"hardwareRevision" text, "numberOfAddOnModules" text, "operationalVlanId"
text, "adminVlanId" text, amps text, "e911Location" text, "messageWaiting"
text, "expansionModule1" text, "expansionModule2" text, "spanToPCPort"
text, "pcVlan" text, "messagesUrl" text, "authenticationUrl" text,
"proxyServerUrl" text, "idleUrl" text, "servicesUrl" text, "directoriesUrl"
text, "informationUrl" text, "loginUserId" text)
) as B
where A.id=sys_id ::bigint)) ;
IF counter > 1 then
outer_query:=outer_query || 'UNION' || '('|| per_inventory_query ||')';
ELSE
outer_query:= '('|| per_inventory_query ||')';
END IF;
counter:=counter + 1;
END Loop;
if counter = 0 then
RETURN;
else
--RAISE NOTICE ' Query is %s ..', outer_query ;
RETURN QUERY EXECUTE outer_query;
RETURN;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION foo_bar()
OWNER TO blah;
-----------------
select * from foo_bar()
------------------
ERROR: subquery must return only one column
LINE 1: SELECT outer_query || 'UNION' || (select
^
QUERY: SELECT outer_query || 'UNION' || (select
A.company_name, A.id as system_id,A.name as system_name,B.*
from
fetch_cucm_systems() as A
cross join
(
SELECT t1.devicepkid,
t1.devicepoolpkid,
t1."devicePoolName",
t1."primaryCallManager",
t1."activeCallManager",
t1."callManagerGroup",
t1."directoryNumberName",
t1.shared_flag,
t1."deviceName",
t1."webInfoExtracted",
t1."deviceDescription",
t1."modelName",
t1."deviceProtocol",
t1."deviceCSS",
t1."dnCSS",
t1."aarCSS",
t1."aarGroup",
t1."mediaResourceGroupList",
t1."userMohAudioSource",
t1."networkMohAudioSource",
t1.location,
t1."userLocale",
t1."networkLocale",
t1."deviceSecurityMode",
t1."extensionMobility",
t1.logintime,
t1."phoneUserName",
t1.ctienabled,
t1."phoneTemplate",
t1."softkeyTemplate",
t1."modelNumber",
t1."retryVideoCallAsAudio",
t1."disableSpeaker",
t1."disableSpeakerAndHeadset",
t1."forwardingDelay",
t1."pcPort",
t1."settingsAccess",
t1.garp,
t1."voiceVlanAccess",
t1."videoCapability",
t1."autoSelectLineEnable",
t1."webAccess",
t1."lastRegistrationDate",
t1."registrationState",
t1."dateDiscovered",
t1."ipAddress",
t1."pcPortConfiguration",
t1."accessPortInformation",
t1."swPortConfiguration",
t1."networkPortInformation",
t1."neighborDeviceId",
t1."neighborIpAddress",
t1."neighborPort",
t1."subnetMask",
t1."networkSegment",
t1."dhcpEnabled",
t1."dhcpServer",
t1.tftpserver1,
t1.tftpserver2,
t1.alttftpserver,
t1.securitymode,
t1."defaultRouter1",
t1."domainName",
t1."dnsServer1",
t1."dnsServer2",
t1.phoneload,
t1."appLoadId",
t1.defaultsccpload,
t1.defaultsipload,
t1."bootLoadId",
t1.version,
t1."serialNumber",
t1."hardwareRevision",
t1."numberOfAddOnModules",
t1."operationalVlanId",
t1."adminVlanId",
t1.amps,
t1."e911Location",
t1."messageWaiting",
t1."expansionModule1",
t1."expansionModule2",
t1."spanToPCPort",
t1."pcVlan",
t1."messagesUrl",
t1."authenticationUrl",
t1."proxyServerUrl",
t1."idleUrl",
t1."servicesUrl",
t1."directoriesUrl",
t1."informationUrl",
t1."loginUserId"
FROM dblink ('dbname = '|| db_name ||'::text user=blah
password=blah123'::text, 'select * from v_detailed_phone_inventory'::text )
t1(devicepkid text, devicepoolpkid text, "devicePoolName" text,
"primaryCallManager" text, "activeCallManager" text,
"callManagerGroup" text, "directoryNumberName" text, shared_flag text,
"deviceName" text, "webInfoExtracted" text, "deviceDescription" text,
"modelName" text, "deviceProtocol" text, "deviceCSS" text, "dnCSS" text,
"aarCSS" text, "aarGroup" text, "mediaResourceGroupList"
text, "userMohAudioSource" text, "networkMohAudioSource" text, location
text, "userLocale" text, "networkLocale" text, "deviceSecurityMode" text,
"extensionMobility" text, logintime text, "phoneUserName"
text, ctienabled text, "phoneTemplate" text, "softkeyTemplate" text,
"modelNumber" text, "retryVideoCallAsAudio" text, "disableSpeaker" text,
"disableSpeakerAndHeadset" text, "forwardingDelay" text, "pcPort" text,
"settingsAccess" text, garp text, "voiceVlanAccess" text,
"videoCapability" text, "autoSelectLineEnable" text, "webAccess" text,
"lastRegistrationDate" text, "registrationState" text, "dateDiscovered"
text, "ipAddress" text, "pcPortConfiguration" text,
"accessPortInformation" text, "swPortConfiguration" text,
"networkPortInformation" text, "neighborDeviceId" text, "neighborIpAddress"
text, "neighborPort" text, "subnetMask" text, "networkSegment" text,
"dhcpEnabled" text, "dhcpServer" text, tftpserver1 text,
tftpserver2 text, alttftpserver text, securitymode text, "defaultRouter1"
text, "domainName" text, "dnsServer1" text, "dnsServer2" text, phoneload
text, "appLoadId" text, defaultsccpload text, defaultsipload text,
"bootLoadId" text, version text, "serialNumber" text,
"hardwareRevision" text, "numberOfAddOnModules" text, "operationalVlanId"
text, "adminVlanId" text, amps text, "e911Location" text, "messageWaiting"
text, "expansionModule1" text, "expansionModule2" text,
"spanToPCPort" text, "pcVlan" text, "messagesUrl" text, "authenticationUrl"
text, "proxyServerUrl" text, "idleUrl" text, "servicesUrl" text,
"directoriesUrl" text, "informationUrl" text, "loginUserId" text)
) as B
where A.id=sys_id ::bigint)
CONTEXT: PL/pgSQL function foo_bar() line 18 at assignment
********** Error **********
ERROR: subquery must return only one column
SQL state: 42601
Context: PL/pgSQL function foo_bar() line 18 at assignment
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-06-11 16:25:37 | Re: Need help. Getting an error in my function when I do a select * from foo_bar() |
Previous Message | David G Johnston | 2014-06-06 18:56:26 | Re: WHERE condition lost from conditional unique index |