Need help. Getting an error in my function when I do a select * from foo_bar() - Mailing list pgsql-novice
| From | Shubhra Sharma |
|---|---|
| Subject | Need help. Getting an error in my function when I do a select * from foo_bar() |
| Date | |
| Msg-id | CABFOHTxvs5t1rJF7vdV=vsChFh7iXBtu-hFC-kZsgvPsm34_Vw@mail.gmail.com Whole thread Raw |
| Responses |
Re: Need help. Getting an error in my function when I do a select *
from foo_bar()
(David G Johnston <david.g.johnston@gmail.com>)
|
| List | 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;
-- 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()
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
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
pgsql-novice by date: