-- 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
