Обсуждение: PL/pgsql

Поиск
Список
Период
Сортировка

PL/pgsql

От
"Tuttle, Gene"
Дата:
I am new to Postgresql and am having problems getting functions working.

I have been following an example in the book "PostgreSQL" by Douglas &
Douglas Published by Developers Library.
I put the  code in as a function (see end of email)


When I execute it from psql I get the following:
dev=# select my_factorial(1);
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Can any one help me find the problem.  point me in the right direction?

Thanks
Gene Tuttle




-- Function: public.my_factorial(int4)

-- DROP FUNCTION public.my_factorial(int4);

CREATE OR REPLACE FUNCTION public.my_factorial(int4)
  RETURNS int4 AS
'
  DECLARE
    arg INTEGER;
  BEGIN

    arg := $1;

    IF arg IS NULL or arg < 0 THEN
        RAISE NOTICE \'Invalid Number\';
        RETURN NULL;
    ELSE
        IF arg = 1 THEN
            RETURN 1;
        ELSE
            DECLARE
                next_value INTEGER;
            BEGIN


                next_value := my_factorial(arg - 1) * arg;
                RETURN next_value;
            END;
        END IF;
    END IF;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

Re: PL/pgsql

От
"Bender, Cheryl"
Дата:
Gene;

This is probably not much help but I was able to create and run this
function on 7.3.2 (running on FreeBSD 5.1) verbatim from your message.
Perhaps the function is corrupted?  Maybe try dropping and recreating
the function.

Of course you wouldn't be able to factorial anything bigger than about
11 or 12 using this function because of the int4 datatype, but this
shouldn't be an issue with an arg of 1.  Even feeding a number like 20
that caused an overflow did not cause my server to crash--just returned
a negative number.

Cheryl Bender


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tuttle, Gene
Sent: Tuesday, June 29, 2004 3:53 PM
To: 'pgsql-admin@postgresql.org'
Subject: [ADMIN] PL/pgsql


I am new to Postgresql and am having problems getting functions working.

I have been following an example in the book "PostgreSQL" by Douglas &
Douglas Published by Developers Library. I put the  code in as a
function (see end of email)


When I execute it from psql I get the following:
dev=# select my_factorial(1);
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Can any one help me find the problem.  point me in the right direction?

Thanks
Gene Tuttle




-- Function: public.my_factorial(int4)

-- DROP FUNCTION public.my_factorial(int4);

CREATE OR REPLACE FUNCTION public.my_factorial(int4)
  RETURNS int4 AS
'
  DECLARE
    arg INTEGER;
  BEGIN

    arg := $1;

    IF arg IS NULL or arg < 0 THEN
        RAISE NOTICE \'Invalid Number\';
        RETURN NULL;
    ELSE
        IF arg = 1 THEN
            RETURN 1;
        ELSE
            DECLARE
                next_value INTEGER;
            BEGIN


                next_value := my_factorial(arg - 1) *
arg;
                RETURN next_value;
            END;
        END IF;
    END IF;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend