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