Re: Need help with this Function. I'm getting an error - Mailing list pgsql-novice
| From | David G Johnston |
|---|---|
| Subject | Re: Need help with this Function. I'm getting an error |
| Date | |
| Msg-id | CAKFQuwa_C59eL+jJWLGX6Eg62=af3gbadQ1WsG0NAKCovy7HXg@mail.gmail.com Whole thread Raw |
| In response to | Re: Need help with this Function. I'm getting an error (ssharma <sharma.shubhra07@gmail.com>) |
| Responses |
Re: Need help with this Function. I'm getting an error
(ssharma <sharma.shubhra07@gmail.com>)
|
| 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.
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.
pgsql-novice by date: