From: | Shubhra Sharma <sharma(dot)shubhra07(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Need help with this Function. I'm getting an error |
Date: | 2014-06-11 20:40:07 |
Message-ID: | CABFOHTwiTOrsbaiFai=XHix-7Mw8bheHWC0p0quHkEy8MFTnNA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
-- 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
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-06-11 20:41:48 | Re: I probably don't understand aggregates. |
Previous Message | Markus Neumann | 2014-06-11 19:55:01 | I probably don't understand aggregates. |