Postgres Pro
Downloads
Home   >   mailing lists

Thread: Simple function

Simple function

From
Christine Penner
Date:
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

Re: Simple function

From
Adrian Klaver
Date:



----- "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

Re: Simple function

From
Richard Broersma
Date:
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

Re: Simple function

From
Christine Penner
Date:
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