Thread: a problem when poring from Oracle's PL/SQL to PLPGSQL

pgsql-hackers:

The following is Oracle's PL/SQL
 if resTypeTableName is nullthen                                                               queryStr := 'select
IntIID,Path FROM aaResourceData' || ' where ResType=''' || srcType || ''' and ResID=''' || srcID || '''';
                                                else
                                                     queryStr := 'select IntIID, Path FROM ' || resTypeTableName || '
whereResType=''' || srcType || ''' and ResID=''' ||                           srcID || '''';
       end if;                                                         
open cursorSrc for queryStr;


Here queryStr is a variable which type is TEXT OR VARCHAR or other string types.

But in  PLPGSQL, we can only open a cursor this way:
    open cursorSrc for select * from testtable;

We cannot substitude "select * from testtable" with a variable.

Is there another way to handle it?

Thank you for your help. :-)


　　　　　　　　billy
　　　　　　　　billywq@163.com
　　　　　　　　　　2008-06-13

billy schrieb:
> pgsql-hackers:
>
> The following is Oracle's PL/SQL
>
>   if resTypeTableName is null
>     then                                                            
>     queryStr := 'select IntIID, Path FROM aaResourceData' || ' where ResType=''' || srcType || ''' and ResID=''' ||
srcID|| '''';                                                           
 
>       else
             queryStr := 'select IntIID, Path FROM ' || resTypeTableName || ' where ResType=''' || srcType || ''' and
ResID='''||            
 
>                 srcID || '''';                                   
>   end if;                                                        
>
>  open cursorSrc for queryStr;
>
>
> Here queryStr is a variable which type is TEXT OR VARCHAR or other string types.
>
> But in  PLPGSQL, we can only open a cursor this way:
>
>      open cursorSrc for select * from testtable;
>
> We cannot substitude "select * from testtable" with a variable.
>
> Is there another way to handle it?
>
> Thank you for your help. :-)
>   
open cursorSrc for execute queryStr; should work fine

Regards
Mario Weilguni