Dynamic Query problem

Started by Dinesh Parikhover 24 years ago4 messagesgeneral
Jump to latest
#1Dinesh Parikh
dineshp@newgen.co.in

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

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Dinesh Parikh (#1)
Re: Dynamic Query problem

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

#3Dinesh Parikh
dineshp@newgen.co.in
In reply to: Stephan Szabo (#2)
Re: Dynamic Query problem

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

#4Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Dinesh Parikh (#3)
Re: Dynamic Query problem

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.