Обсуждение: Simple function
Hi, I'm trying to create a simple function but having a bit of trouble. This is what I want to do. I want to pass a key as a parameter (BKEY integer) the code in the function should be count(*) from F_BUILDINGS where B_PRIMARY_SEQ=BKEY I want to return the count from the select statement. Christine Penner Ingenious Software 250-352-9495 <mailto:christine@ingenioussoftware.com>christine@ingenioussoftware.com
----- "Christine Penner" <christine@ingenioussoftware.com> wrote: > Hi, > > I'm trying to create a simple function but having a bit of trouble. > This is what I want to do. > > I want to pass a key as a parameter (BKEY integer) > the code in the function should be > count(*) from F_BUILDINGS where B_PRIMARY_SEQ=BKEY > > I want to return the count from the select statement. > > Christine Penner > Ingenious Software > 250-352-9495 > <mailto:christine@ingenioussoftware.com>christine@ingenioussoftware.com > Something along lines of: CREATE OR REPLACE FUNCTION public.count_test(integer) RETURNS bigint LANGUAGE sql AS $function$ select count(*) from F_BUILDINGS where B_PRIMARY_SEQ=$1; $function$ Adrian Klaver aklaver@comcast.net
On Wed, Dec 23, 2009 at 9:03 AM, Christine Penner <christine@ingenioussoftware.com> wrote: > Hi, > > I'm trying to create a simple function but having a bit of trouble. This is > what I want to do. > > I want to pass a key as a parameter (BKEY integer) > the code in the function should be > count(*) from F_BUILDINGS where B_PRIMARY_SEQ=BKEY > > I want to return the count from the select statement. It works for me: postgres=# CREATE OR REPLACE FUNCTION countertest(integer) RETURNS bigint AS $$ SELECT COUNT(*) FROM test WHERE nbr = $1; $$ LANGUAGE SQL; CREATE FUNCTION postgres=# select countertest(24); countertest ------------- 1 (1 row) http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Thanks a lot, that worked great. Saved me a lot of time trying to figure it out too. Christine >It works for me: > >postgres=# CREATE OR REPLACE FUNCTION countertest(integer) RETURNS >bigint AS $$ SELECT COUNT(*) FROM test WHERE nbr = $1; $$ LANGUAGE >SQL; >CREATE FUNCTION >postgres=# select countertest(24); > countertest >------------- > 1 >(1 row) > > >http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html > > >-- >Regards, >Richard Broersma Jr. > >Visit the Los Angeles PostgreSQL Users Group (LAPUG) >http://pugs.postgresql.org/lapug