Thread: plpgsql functions

On 15/08/2008 20:12, c k wrote:
> Hi,
> I am getting an error for a function written in plpgsql, as -
>
> CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer)
>   RETURNS SETOF uf_closingbal AS

<snip>

For functions return SETOF any type, you need to use the following idiom:

...
declare
   rec yourtype;
begin
   for rec in [your query here...]
   loop
     return next rec;
   end loop;
   return;
end;
...

See the docs on control structures in pl/pgsql.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Aug 15, 2008, at 1:47 PM, Raymond O'Donnell wrote:
> For functions return SETOF any type, you need to use the following
> idiom:

Or, you can use,

    RETURN QUERY <your query here>

2008/8/15 c k <shreeseva.learning@gmail.com>:
> Hi,
> I am getting an error for a function written in plpgsql, as -
>
> CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer)
>   RETURNS SETOF uf_closingbal AS
> $BODY$begin
> select accgroups."accgroupid", COALESCE(sum(osc),0) as obc,
> COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as sc,
> COALESCE(sum(csd),0) as sd, (COALESCE(sum(osc),0)+COALESCE(sum(csc),0)) as
> cc, (COALESCE(sum(osd),0)+COALESCE(sum(csd),0)) as cd,
> accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount",
> accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid",
> accgroups."accobjecttype", accgroups."againstid" from (accgroups left join
> (select * from uf_accgroupob($1)) as accob on
> accgroups."accgroupid"=accob."accgroupid") left join (select * from
> uf_accgroupcurrentbal($1, $2)) as accgcb2 on accgroups."accgroupid" =
> accgcb2."accgroupid"  group by accgroups."accgroupid",
> accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount",
> accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid",
> accgroups."accobjecttype", accgroups."againstid";
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100
>   ROWS 1000;
> giving me an error when called from another function as 'query has no
> destination for result data'.
> Why? Please give the details about creating functions having only SELECT
> statements using plpgsql?, What points to be remembered?

pl/pgsql isn't SQL/PSM - so there are not allowed one statement
functions. Use SQL language instead.

Regards
Pavel Stehule

>
> Thanks
> CPK
>