Dynamic Query problem
Dear All,
I am new in postgres there for i have some problems. I am executing some type of Business rule in strored procedure using language plpgsql. When I am executing a Dynamic Query then It return an error which is some how unable to understand Please Help me It is urgent . I am attaching my code as well as error which appears to me.
I have noted that if table has same type of column then no error comes .
I am using RedHat 7.1 and postgres 7.1.2
Thanks
Regards
Dinesh Parikh
NSTL, New Delhi.
Function Code is described as:
drop function testfunc();
Create function testfunc()
returns int4 as '
Declare
Query text;
MyRec Record;
DBColName Text;
DBOut Text;
DBCount int4 := 1;
Begin
DBColName := ''X_1'';
For i In 1 ..10
Loop
If (DBCount =1 ) Then
DBColName := ''X_2'';
DBCount :=2;
Else
DBColName := ''X_1'';
DBCount := 1;
End If;
Query := '' Select ''|| DBColName || '' As Field '' || '' From TestTable '';
For MyRec In Execute Query
Loop
Raise Notice '' MyRec.Field = %'',MyRec.Field;
DBOut := MyRec.Field;
Raise Notice ''DBOut = %'',DBOut;
End Loop
End Loop;
Return null;
end;
'language 'plpgsql';
I have a table TestTable(X_1 int4, X_2 Int8);
Error Appeared as:
Jurassik=# select testfunc();
NOTICE: MyRec.Field = 211
NOTICE: DBOut = 211
NOTICE: MyRec.Field = 221
NOTICE: DBOut = 221
NOTICE: MyRec.Field = 231
NOTICE: DBOut = 231
NOTICE: MyRec.Field = 241
NOTICE: DBOut = 241
NOTICE: MyRec.Field = 21
ERROR: type of myrec.field doesn't match that when preparing the plan
On Fri, 28 Sep 2001, Dinesh Parikh wrote:
Dear All,
I am new in postgres there for i have some problems. I am executing
some type of Business rule in strored procedure using language
plpgsql. When I am executing a Dynamic Query then It return an error
which is some how unable to understand Please Help me It is urgent . I
am attaching my code as well as error which appears to me.
I have noted that if table has same type of column then no error comes
.
My guess would be that the offending line is the DBOut := MyRec.field
since when you go from X_1 to X_2 the right hand side changes type.
It probably planned out how to do the assignment/conversion for the
int4 column and then an int8 is there.
Show quoted text
ERROR: type of myrec.field doesn't match that when preparing the plan
Hi,
Actually I think there is some type of caching in record datatype. Is there
any way to find datatype of record's column so that i can explicit cast in
assignment(have u propse alternate of this).
Regards
Dinesh
NSTL, New Delhi
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Dinesh Parikh" <dineshp@newgen.co.in>
Cc: <pgsql-general@postgresql.org>
Sent: Saturday, September 29, 2001 12:02 PM
Subject: Re: [GENERAL] Dynamic Query problem
Show quoted text
On Fri, 28 Sep 2001, Dinesh Parikh wrote:
Dear All,
I am new in postgres there for i have some problems. I am executing
some type of Business rule in strored procedure using language
plpgsql. When I am executing a Dynamic Query then It return an error
which is some how unable to understand Please Help me It is urgent . I
am attaching my code as well as error which appears to me.I have noted that if table has same type of column then no error comes
.My guess would be that the offending line is the DBOut := MyRec.field
since when you go from X_1 to X_2 the right hand side changes type.
It probably planned out how to do the assignment/conversion for the
int4 column and then an int8 is there.ERROR: type of myrec.field doesn't match that when preparing the plan
On Sat, 29 Sep 2001, Dinesh Parikh wrote:
Hi,
Actually I think there is some type of caching in record datatype. Is there
any way to find datatype of record's column so that i can explicit cast in
assignment(have u propse alternate of this).
Well, you could explicitly cast in the select query string to the type you
want it to be. Something like:
''Select '' || DBColName || ''::<type> As Field '' || '' From TestTable''
where <type> is the type you want out.