Thread: 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