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

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