Thread: cursor with dinamic string
Hi all, I need use dinamic string for my cursor... My code is: query varchar := 'field1, field2 from ''' || tableName ||''' ........; //has dinamic params then I create cursor... myCursor CURSOR FOR SELECT query; I compile my function but when i run it I obtain error... ERROR: invalid input syntax for integer: ...... CONTEXT: PL/pgSQL function "my_function" line 72 at FETCH If I use static string, I don't have problems!!! Can I solve it? Thanks in advance -- Luca Santaniello email: luca.santaniello.81@gmail.com skype: luca.santaniello msn: luketto81@hotmail.com
Hello 2011/3/25 Luca Santaniello <luca.santaniello.81@gmail.com>: > Hi all, > > I need use dinamic string for my cursor... My code is: > > query varchar := 'field1, field2 from ''' || tableName ||''' ........; //has > dinamic params > use a FOR EXECUTE statement FOR r IN EXECUTE 'SELECT .. FROM ' || quote_ident(tableName) || ' ....' LOOP ... you can use OPEN FOR EXECUTE too, but FOR statement is preferable Regards Pavel Stehule http://www.postgresql.org/docs/9.0/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING > then I create cursor... > > myCursor CURSOR FOR SELECT query; > > I compile my function but when i run it I obtain error... > > ERROR: invalid input syntax for integer: ...... > CONTEXT: PL/pgSQL function "my_function" line 72 at FETCH > > If I use static string, I don't have problems!!! > > Can I solve it? > > Thanks in advance > > -- > Luca Santaniello > email: luca.santaniello.81@gmail.com > skype: luca.santaniello > msn: luketto81@hotmail.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Thank you very much > Hello > > 2011/3/25 Luca Santaniello<luca.santaniello.81@gmail.com>: >> Hi all, >> >> I need use dinamic string for my cursor... My code is: >> >> query varchar := 'field1, field2 from ''' || tableName ||''' ........; //has >> dinamic params >> > use a FOR EXECUTE statement > > FOR r IN EXECUTE 'SELECT .. FROM ' || quote_ident(tableName) || ' ....' > LOOP > ... > > you can use OPEN FOR EXECUTE too, but FOR statement is preferable > > Regards > > Pavel Stehule > > http://www.postgresql.org/docs/9.0/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING > >> then I create cursor... >> >> myCursor CURSOR FOR SELECT query; >> >> I compile my function but when i run it I obtain error... >> >> ERROR: invalid input syntax for integer: ...... >> CONTEXT: PL/pgSQL function "my_function" line 72 at FETCH >> >> If I use static string, I don't have problems!!! >> >> Can I solve it? >> >> Thanks in advance >> >> -- >> Luca Santaniello >> email: luca.santaniello.81@gmail.com >> skype: luca.santaniello >> msn: luketto81@hotmail.com >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> -- Luca Santaniello email: luca.santaniello.81@gmail.com | luca.santaniello@email.it phone: +39 3470373994 skype: luca.santaniello msn: luketto81@hotmail.com