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

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

Responses

Browse pgsql-novice by date

  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.