Thread: Function error

Function error

From
"Vishal Kashyap @ [Sai Hertz And Control Systems]"
Date:
Dear PostgreSQL enlightened ,

I have an error with this function can anyone suggest me what stupid
thing am i doing

<code>
create type_a as (mnthtot numeric(20,2),monthtxt varchar(20));

CREATE OR REPLACE FUNCTION "public"."func_ca" () RETURNS "public"."type_a" AS'
DECLARE
f_type_a type_a%ROWTYPE;
f_data record;
f_year int8;
f_month int8;
BEGIN
f_year := 2004 ;--hard coded for year 2004-2005
f_month := 4 ;--hard coded for year 2004-2005
f_type_a.mnthtot := 0;-- Month total
f_type_a.monthtxt:= ''MONTH''::varchar;-- Month text
for f_data IN select sum(cust_wdrwl_amt) as total,4 as month from
cust_lon_dtls where (select extract(MONTHS from
cust_lon_dtls.curr_rn_dt)) = f_month AND (select extract(YEARS from
cust_lon_dtls.curr_rn_dt)) = f_year
LOOP
f_type_a.mnthtot := f_data.total;
f_type_a.monthtxt := (f_data.month || ''MONTH'')::varchar;
f_month := f_month + 1;
IF f_month = 13 THEN
f_month := 1 ;
f_year := 2005;
elsif f_month = 5 AND f_year = 2005 THEN
return f_type_a;
END IF;
END LOOP;
return f_type_a;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
</code>


The error is

somedata =# select sai_func_ca();
ERROR:  cannot display a value of type record




--
With Best Regards,
Vishal Kashyap.
Did you know SaiPACS is one and only PACS
Management tool.
http://saihertz.com

Re: Function error

From
Tom Lane
Date:
"Vishal Kashyap @ [Sai Hertz And Control Systems]" <vishalonlist@gmail.com> writes:
> CREATE OR REPLACE FUNCTION "public"."func_ca" () RETURNS "public"."type_a" AS'
> ...
> somedata =# select sai_func_ca();
> ERROR:  cannot display a value of type record

Try
    select * from sai_func_ca();

            regards, tom lane

Re: Function error

From
"Vishal Kashyap @ [Sai Hertz And Control Systems]"
Date:
Dear Tom


> > ...
> > somedata =# select sai_func_ca();
> > ERROR:  cannot display a value of type record
>
> Try
>         select * from sai_func_ca();


I knew I was doing something stupid, today was not my day definatly.
Anyways thanks Tom.

--
With Best Regards,
Vishal Kashyap.
Did you know SaiPACS is one and only PACS
Management tool.
http://saihertz.com