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

Shubhra Sharma wrote
> -- 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

http://www.postgresql.org/docs/9.3/static/sql-select.html

[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ (
column_alias [, ...] | column_definition [, ...] ) ]

The error message tells you everything else you need to know.

Try running the indicated query outside of a function as see if that helps
you identify you mistake.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Need-help-with-this-Function-I-m-getting-an-error-tp5806884p5806887.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.