Обсуждение: Need help on SP
Hello All, I want to store count(*) of a table in a variable , for that I have declared a variable and wrote a statment but it is giving me error. can anybody please help me.. CREATE OR REPLACE FUNCTION foreign_keys_tables(OUT par_result charecter varying, IN par_tablename character varying , IN par_clomnname,IN par_colvalue integer) AS $BODY$) DECALRE countno integer; BEGIN Select count(*) into countno from par_tablename where par_columnname=par_colvalue; if countno>0 then par_result='yes' else par_result='No' end if END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; it is giving me error in select statment, Thanks in advance With Regards Ashish Karalkar ____________________________________________________________________________________ Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/
On Tue, Jan 16, 2007 at 05:08:29AM -0800, Ashish Karalkar wrote: > Hello All, > I want to store count(*) of a table in a variable , > for that I have declared a variable and wrote a > statment but it is giving me error. > can anybody please help me.. It would help immensly if you showed us the actual error message. At the moment my best guess is a typo in the first line: > DECALRE ^^^^^^^ Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
> I want to store count(*) of a table in a variable , > for that I have declared a variable and wrote a > statment but it is giving me error. > can anybody please help me.. > > CREATE OR REPLACE FUNCTION foreign_keys_tables(OUT > par_result charecter varying, IN par_tablename > character varying , IN par_clomnname,IN par_colvalue > integer) AS > $BODY$) > DECALRE This is obviously a typo, as has been pointed out. > countno integer; > BEGIN > Select count(*) into countno from par_tablename where > par_columnname=par_colvalue; You cannot use a variable as tablename in static SQL. You will have to use dynamic SQL (EXECUTE '...') for that. > if countno>0 then > par_result='yes' > else > par_result='No' > end if > > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; Yours, Laurenz Albe