Search Postgresql Archives

Cursor body?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello all, 
I am trying to get the list of all foreign key tables
for a given primary table after getting this list I
want to filter out the name of table for which a
particular Primary table.primary key exsits in the
foreign table.foreign key column.
For that I have written a SP but the first cursor
which gives me list of Foreign key table is not
getting iterated. The SP  is working fine for only
first table returned by the cursor.
In short what i can point out is cursor is not
fetching  next rows.
The Sp is attached with this. Please help me.

CREATE OR REPLACE FUNCTION
sp_gen_foreign_keys_tables(out par_childtables text,
IN par_colname character varying , IN par_colvalue
integer) AS
$BODY$

 DECLARE
           curforeign refcursor ;
	   curforeign1 refcursor;
	   tablename text;
	   columnname text;
	   var_str1 text;
	   var_str2 text;
	   countno integer;
BEGIN	
var_str1:='select distinct (fk_relation),fk_column
from foreign_keys where pk_relation in (select
pk_relation from foreign_keys where pk_column='''||
par_colname||''')';

open curforeign for execute var_str1;
FETCH  curforeign  into tablename,columnname ;
var_str2:='select count(*) from '|| tablename || '
where ' || columnname ||' = '|| par_colvalue;
open curforeign1 for execute var_str2;
FETCH  curforeign1 into countno;
close curforeign1;
if countno > 0 then
par_childtables:=par_childtables + ',' + tablename;
end if ;

close curforeign ;   
        
     

END; 
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

What i want is to iterate through the block from
"FETCH  curforeign "  to "end if"


also insted of using second cursor to get the count i
used "select into (variable)" but it gives me error 
"Execute of select into is not implemented yet"
I am using postgreSQL 8.2.

Thanks in advance

With Regards
Ashish Karalkar








 
____________________________________________________________________________________
Never Miss an Email
Stay connected with Yahoo! Mail on your mobile.  Get started!
http://mobile.yahoo.com/services?promote=mail


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux