Thread: Dynamic Query problem

Dynamic Query problem

From

"Dinesh Parikh"

Date:

29 September 2001, 06:58:59

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