Re: Need help with this Function. I'm getting an error

On Wed, Jun 11, 2014 at 7:19 PM, ssharma [via PostgreSQL] <
ml-node+s1045698n5806905h79(at)n5(dot)nabble(dot)com> wrote:

> 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 *
> from
> ​​
> v_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.​

http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

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.

http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

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: http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5806915.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.