Re: plpgsql functions

2008/8/15 c k <shreeseva(dot)learning(at)gmail(dot)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
>