Skip site navigation (1) Skip section navigation (2)

Dynamic Query problem

From: "Dinesh Parikh" <dineshp(at)newgen(dot)co(dot)in>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Dynamic Query problem
Date: 2001-09-28 10:47:47
Message-ID: 004301c1480b$02bdae30$f005a8c0@dinesh (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-novice
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

Dinesh Parikh
NSTL, New Delhi.

Function Code is described as:
drop function testfunc();
Create function testfunc()
returns int4 as '
        Query text;
        MyRec   Record;
        DBColName Text;
        DBOut   Text;
        DBCount int4 := 1;
        DBColName := ''X_1'';
        For i In 1 ..10
                If (DBCount =1 ) Then
                        DBColName := ''X_2'';
                        DBCount :=2;
                        DBColName := ''X_1'';
                        DBCount := 1;
                End If;
                Query := '' Select ''|| DBColName || '' As Field '' || '' From TestTable '';
                For MyRec In Execute Query
                    Raise Notice '' MyRec.Field = %'',MyRec.Field;
                    DBOut := MyRec.Field;
                    Raise Notice ''DBOut = %'',DBOut;
                End Loop
        End Loop;
        Return null;
'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



pgsql-novice by date

Next:From: Yeo Eng HeeDate: 2001-09-28 10:49:53
Subject: Problems installing Pg module on Tru64 Unix
Previous:From: Dinesh ParikhDate: 2001-09-28 10:46:09
Subject: Temporary Table Problem

pgsql-general by date

Next:From: Dinesh ParikhDate: 2001-09-28 10:49:53
Subject: Fw: Problem Related to Goto
Previous:From: Dinesh ParikhDate: 2001-09-28 10:46:09
Subject: Temporary Table Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group