Обсуждение: Simple function closes connection to server

Hello all,

PG 8.0.1
Mandrake 10.1
Uniqueidentifier 0.2 type and function instatlled (see
http://gborg.postgresql.org/project/uniqueidentifier/projdisplay.php)


I have a very simple funtion designed to perform a lookup on a 'zone name'
based on a uniqueidentifier:
-----------------------------------8<-------------------------------------------
CREATE OR REPLACE FUNCTION "getZoneName"(text)
  RETURNS text AS
$BODY$DECLARE
     zonename text;
BEGIN
    -- Perform a lookup on OrgID to determine Zone Name
    SELECT INTO zonename "Name" FROM "tblOrganisation" WHERE "OrgID"::text =
$1;
        RETURN zonename;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER;
-----------------------------------8<-------------------------------------------

The "OrgID" is a uniqueidentifier type, so I explicitly cast it.  The
function works fine when I execute it as follows:
-----------------------------------8<-------------------------------------------
SELECT "getZoneName"('51f6c6a0-fee0-43c1-b50c-bd67191cb374')
-----------------------------------8<-------------------------------------------
The zone name is returned.

However, when I execute it as follows, my connection to the db gets closed
and, obviously, there is no result.  In fact, it appears that ALL
connections are closed (this is not good).
-----------------------------------8<-------------------------------------------
SELECT "ZoneID", "getZoneName"("ZoneID"::text) AS "ZoneName"
   FROM "tblOrganisation";
-----------------------------------8<-------------------------------------------
Essentially, I want to use the "ZoneID" (also a uniqueidentifier type) value
to plug into my function.  I thought my function gets processed on a
row-by-row basis (like a strpos function would).

I am hoping that I am missing something quite simple.  Thanks in advance.


Regards,
Jim

_________________________________________________________________
Take advantage of powerful junk e-mail filters built on patented Microsoft�
SmartScreen Technology.

http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines

  Start enjoying all the benefits of MSN� Premium right now and get the
first two months FREE*.

"T- Bone" <jbowen333@hotmail.com> writes:
> However, when I execute it as follows, my connection to the db gets closed
> and, obviously, there is no result.  In fact, it appears that ALL
> connections are closed (this is not good).

> SELECT "ZoneID", "getZoneName"("ZoneID"::text) AS "ZoneName"
>    FROM "tblOrganisation";

Do you have any NULL entries in the ZoneID column?  It looks to me like
the uniqueidentifier package fails to declare its functions as STRICT,
and since most of them aren't actually coded to check for null input
values, they'll crash on nulls.  In particular uniqueidentifier_text
will.

My recommendation would be to mark all those functions as strict
(and get rid of the tests for null input that do exist in a couple).

            regards, tom lane

Thanks Tom.  I rid the NULLs and altered all uniqueidentifier functions to
strict and everything worked fine.

Cheers,
Jim

>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: "T- Bone" <jbowen333@hotmail.com>
>CC: pgsql-novice@postgresql.org, dmitry@taurussoft.org
>Subject: Re: [NOVICE] Simple function closes connection to server Date:
>Mon, 11 Apr 2005 12:37:28 -0400
>
>"T- Bone" <jbowen333@hotmail.com> writes:
> > However, when I execute it as follows, my connection to the db gets
>closed
> > and, obviously, there is no result.  In fact, it appears that ALL
> > connections are closed (this is not good).
>
> > SELECT "ZoneID", "getZoneName"("ZoneID"::text) AS "ZoneName"
> >    FROM "tblOrganisation";
>
>Do you have any NULL entries in the ZoneID column?  It looks to me like
>the uniqueidentifier package fails to declare its functions as STRICT,
>and since most of them aren't actually coded to check for null input
>values, they'll crash on nulls.  In particular uniqueidentifier_text
>will.
>
>My recommendation would be to mark all those functions as strict
>(and get rid of the tests for null input that do exist in a couple).
>
>            regards, tom lane

_________________________________________________________________
Powerful Parental Controls Let your child discover the best the Internet has
to offer.

http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines

  Start enjoying all the benefits of MSN� Premium right now and get the
first two months FREE*.