Thread: Need help. Getting an error in my function when I do a select * from foo_bar()
Need help. Getting an error in my function when I do a select * from foo_bar()
From
Shubhra Sharma
Date:
-- 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
Re: Need help. Getting an error in my function when I do a select * from foo_bar()
From
David G Johnston
Date:
Shubhra Sharma wrote > ERROR: subquery must return only one column > LINE 1: SELECT outer_query || 'UNION' || (select A scalar sub-query can only return a single row and column. Somewhere in that jumble of text you have a scalar sub-query returning more than one column. i.e.: SELECT ..., (SELECT one_column_only FROM ... LIMIT 1) AS valid_scalar_subquery FROM ... SELECT ..., (SELECT col1, col2 FROM ... LIMIT 1) AS invalid_scalar_subquery FROM ... I am thinking your "per_inventory_query:=quote_literal((SELECT ...))" statement may be the culprit since a query result being fed into a function is an instance of a scalar sub-query. I have no way to test/prove since your example is not self-contained. I've spent as much time as I am willing to try and spot it but cannot. You can wait and see if anyone else has better luck/more patience or you can try greatly simplifying and formatting the query which you can either re-post here or in the process of doing discover where the error is yourself. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-Getting-an-error-in-my-function-when-I-do-a-select-from-foo-bar-tp5806849p5806854.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.