Re: Need help with this Function. I'm getting an error - Mailing list pgsql-novice

ok, so that's my bad. It was a typo not having the alias. So let me back up
and explain what I really want to do:
I have a bunch of different databases say db1, db2 ....dbn
I need to dblink to views (that I create) in each of these databases to
another database say meta_db which contains meta data about each of db1,
db2,...dbn while cross joining the meta data to each of the views.
Next I need to union all the cross joins. For this I am trying to write a
function(I'm able to do all this manually with a query, but when I try using
the query it in a function I run into problems). When there is only one
database say db1 I don't need to do a union but when there are more than one
databases say db1 and db23 then I need to do the union, for this I have
tried to use the per_inventory_query and outer_query without success.

1)Is there a better way to write the union part of the query in the function
than the way I am doing?
2) what's the best way to put the following query in quotes so I can assign
it to per_inventory_query

*******
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=db1 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=1
*******
I would like to be able to pass the id  and the db1 as variables unlike the
constants that I have in the above query

********
Thanks a lot,
Shubhra




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5806905.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.