Thread: Need help with this Function. I'm getting an error
-- Function: bar()
-- DROP FUNCTION bar();
CREATE OR REPLACE FUNCTION bar()
RETURNS SETOF text AS
$BODY$
DECLARE
sys_id bigint default 1;
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 from
fetch_cucm_systems()
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 bar()
OWNER TO blah;
================
select * from bar()
===============
ERROR: missing FROM-clause entry for table "a"
LINE 2: A.company_name from
^
QUERY: SELECT quote_literal((select
A.company_name from
fetch_cucm_systems()
where A.id=sys_id ::bigint))
CONTEXT: PL/pgSQL function bar() line 12 at assignment
********** Error **********
ERROR: missing FROM-clause entry for table "a"
SQL state: 42P01
Context: PL/pgSQL function bar() line 12 at assignment
-- DROP FUNCTION bar();
CREATE OR REPLACE FUNCTION bar()
RETURNS SETOF text AS
$BODY$
DECLARE
sys_id bigint default 1;
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 from
fetch_cucm_systems()
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 bar()
OWNER TO blah;
================
select * from bar()
===============
ERROR: missing FROM-clause entry for table "a"
LINE 2: A.company_name from
^
QUERY: SELECT quote_literal((select
A.company_name from
fetch_cucm_systems()
where A.id=sys_id ::bigint))
CONTEXT: PL/pgSQL function bar() line 12 at assignment
********** Error **********
ERROR: missing FROM-clause entry for table "a"
SQL state: 42P01
Context: PL/pgSQL function bar() line 12 at assignment
Shubhra Sharma wrote
> -- Function: bar()
>
> -- DROP FUNCTION bar();
>
> CREATE OR REPLACE FUNCTION bar()
> RETURNS SETOF text AS
> $BODY$
> DECLARE
> sys_id bigint default 1;
> 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 from
> fetch_cucm_systems()
> 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 bar()
> OWNER TO blah;
> ================
>
> select * from bar()
> ===============
> ERROR: missing FROM-clause entry for table "a"
> LINE 2: A.company_name from
> ^
> QUERY: SELECT quote_literal((select
> A.company_name from
> fetch_cucm_systems()
> where A.id=sys_id ::bigint))
> CONTEXT: PL/pgSQL function bar() line 12 at assignment
>
> ********** Error **********
>
> ERROR: missing FROM-clause entry for table "a"
> SQL state: 42P01
> Context: PL/pgSQL function bar() line 12 at assignment
http://www.postgresql.org/docs/9.3/static/sql-select.html
[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ (
column_alias [, ...] | column_definition [, ...] ) ]
The error message tells you everything else you need to know.
Try running the indicated query outside of a function as see if that helps
you identify you mistake.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5806887.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
I am able to run the query successfully outside of the function. I know the problem is with, per_inventory_query:= quote_literal((select A.company_name from fetch_cucm_systems() where A.id=sys_id ::bigint)) ; I was hoping somebody could eyeball the syntax and tell me why select * from bar() becomes select quote_literal((select A.company_name from fetch_cucm_systems() where A.id=sys_id ::bigint)) in my case ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5806892.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
ssharma wrote > I am able to run the query successfully outside of the function. I know > the problem is with, per_inventory_query:= quote_literal((select > A.company_name from > fetch_cucm_systems() > where A.id=sys_id ::bigint)) ; > I was hoping somebody could eyeball the syntax and tell me why select * > from bar() > becomes select quote_literal((select > A.company_name from > fetch_cucm_systems() > where A.id=sys_id ::bigint)) in my case ? The query: select A.company_name from fetch_cucm_systems() where A.id=sys_id::bigint; The only FROM table name present is called "fetch_cucm_systems", it is the same as the function name. But you are trying to refer to table "A" here in both the WHERE clause and the select list. If you really want to call the function result table "A" you need to provide an alias: select A.company_name from fetch_cucm_systems() AS A where A.id=sys_id::bigint; That is the snippet of the SELECT reference page that I quoted to you - the syntax for aliasing a FROM clause item. I don't get what you mean by "select * from bar() becomes select quote_literal [...]" ... It would help, too, if you actually talked and not just throw up code. If you tell us what is confusing you we can provide better help. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5806894.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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.
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.
Suggestion when providing queries like this - or even testing them - just pass/list a handful of critical columns. In terms of posting to a list edit what you send to remove unimportant stuff so that people can quickly focus on the key stuff. See my rewrite below.
*******
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,[lots more columns]
FROM dblink('dbname=db1 user=blah password=blah123'::text, 'select * fromv_detailed_phone_inventory'::text) t1(devicepkid text,[lots more columns])
) 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
********
FROM dblink('dbname=' || v_iter_db || ' user=blah passwordd=blah123', ...)
&
WHERE A.id=v_somenumber
1)Is there a better way to write the union part of the query in the function than the way I am doing?
I would likely forgo a UNION and use looping.
To do so I believe I would need to setup a temporary table first. Then, during each iteration I'd do
INSERT INTO temp_table
SELECT *
FROM dblink(...)
2) what's the best way to put the following query in quotes so I can assign it to per_inventory_query
If you use:
CREATE TEMP TABLE temp_table ...
FOR v_rec IN SELECT * FROM fetch_cucm_systems() LOOP
v_db_id := v_rec.dbid;
INSERT INTO temp_table
SELECT db_qry_result.*, v_rec.company_name, v_rec.id, [other data elements from either the loop record v_rec or the dblink query result]
FROM dblink('dbname=' || v_db_id || ' ...', $qry$
SELECT *
FROM
v_detailed_phone_inventory $qry$
) db_qry_result ( column aliases );
END LOOP;
You should be able to avoid the need to generate and store the text query since you no longer need a union. Given the nature of dblink I doubt a FOR LOOP is going to be any worse performing and it is definitely going to be easier to write and debug.
Re-reading your first post you've got the right idea of using a LOOP but you choose to use it to build up a super-complicated text query. Instead you should just take the relevant context and execute a query and save the results.
Since you are returning a SET/TABLE you could also just use "RETURN NEXT;" instead of creating the temporary table.
Otherwise you will want to issue:
RETURN QUERY SELECT * FROM temp_table;
at the end of the function to dump out the results.
David J.
View this message in context: Re: Need help with this Function. I'm getting an error
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Thanks for your response and sorry for my late response. Was away from my
machine. I used your suggestion to complete do away with the union and
instead used the for loop. Also I did not use a temp table or return next. I
got it to work with return query instead. Here's what it looks like:
-- Function: foo_bar()
-- DROP FUNCTION foo_bar();
CREATE OR REPLACE FUNCTION foo_bar()
RETURNS SETOF detailed_phone_inventory_type AS
$BODY$
DECLARE
v_iter_sys_id bigint ;
BEGIN
FOR v_inter_sys_id IN SELECT distinct system_id FROM
inventory_system_properties
LOOP
return 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,
[lots more columns]
FROM dblink('dbname=db'||v_iter_sys_id||' user=blah
password=blah123'::text, 'select * from
v_detailed_phone_inventory'::text) t1(devicepkid text,
[lots more columns]
)
) as B
where A.id=v_iter_sys_id::bigint);
END LOOP;
RETURN;
END;
$BODY$
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5807338.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Excellent. This more elegant than the union.
- Gerald
On 6/15/2014 1:22 PM, ssharma wrote:
> Thanks for your response and sorry for my late response. Was away from my
> machine. I used your suggestion to complete do away with the union and
> instead used the for loop. Also I did not use a temp table or return next. I
> got it to work with return query instead. Here's what it looks like:
>
> -- Function: foo_bar()
>
> -- DROP FUNCTION foo_bar();
>
> CREATE OR REPLACE FUNCTION foo_bar()
> RETURNS SETOF detailed_phone_inventory_type AS
> $BODY$
> DECLARE
> v_iter_sys_id bigint ;
>
> BEGIN
> FOR v_inter_sys_id IN SELECT distinct system_id FROM
> inventory_system_properties
>
> LOOP
> return 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,
> [lots more columns]
>
> FROM dblink('dbname=db'||v_iter_sys_id||' user=blah
> password=blah123'::text, 'select * from
>
> v_detailed_phone_inventory'::text) t1(devicepkid text,
> [lots more columns]
> )
> ) as B
> where A.id=v_iter_sys_id::bigint);
> END LOOP;
> RETURN;
> END;
> $BODY$
>
>
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5807338.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>
--
siamo arrivati sani e salvi