Обсуждение: Casting

I'm obviously missing something here but I don't know what. This is on 7.3.2.
When I use my cast I get:

mydb=> select cast('truE'::text as boolean);
WARNING:  Error occurred while executing PL/pgSQL function text_to_bool
WARNING:  line 4 at select into variables
ERROR:  CopyQuerySnapshot: no snapshot has been set

Any clues as to why that is?

The code is simplistic to say the least:


-- text_to_bool ()
--
-- Cast from text type to boolean.
--
CREATE OR REPLACE FUNCTION text_to_bool ( text )
    RETURNS boolean
    AS '
    DECLARE
        rv    boolean;
    BEGIN
        SELECT INTO rv
            CASE
                WHEN lower($1) = ''true''
                    OR lower($1) = ''t''
                    OR lower($1) = ''1''
                THEN
                    true
                WHEN lower($1) = ''false''
                    OR lower($1) = ''f''
                    OR lower($1) = ''0''
                THEN
                    false
                ELSE
                    NULL
                END;
        RETURN rv;
    END;
    '
    LANGUAGE 'plpgsql'
    STRICT
    IMMUTABLE;

CREATE CAST (text AS boolean) WITH FUNCTION text_to_bool ( text );


Thanks,

--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> mydb=> select cast('truE'::text as boolean);
> WARNING:  Error occurred while executing PL/pgSQL function text_to_bool
> WARNING:  line 4 at select into variables
> ERROR:  CopyQuerySnapshot: no snapshot has been set

I've applied the attached patch to fix this.

            regards, tom lane

*** REL7_3/src/backend/parser/parse_coerce.c.orig    Thu Oct 24 18:09:00 2002
--- REL7_3/src/backend/parser/parse_coerce.c    Wed Apr  9 22:47:57 2003
***************
*** 226,251 ****
                  result = (Node *) makeRelabelType(result, targetTypeId, -1,
                                                    cformat);
              }
-
-             /*
-              * If the input is a constant, apply the type conversion
-              * function now instead of delaying to runtime.  (We could, of
-              * course, just leave this to be done during
-              * planning/optimization; but it's a very frequent special
-              * case, and we save cycles in the rewriter if we fold the
-              * expression now.)
-              *
-              * Note that no folding will occur if the conversion function is
-              * not marked 'immutable'.
-              *
-              * HACK: if constant is NULL, don't fold it here.  This is needed
-              * by make_subplan(), which calls this routine on placeholder
-              * Const nodes that mustn't be collapsed.  (It'd be a lot
-              * cleaner to make a separate node type for that purpose...)
-              */
-             if (IsA(node, Const) &&
-                 !((Const *) node)->constisnull)
-                 result = eval_const_expressions(result);
          }
          else
          {
--- 226,231 ----
***************
*** 496,502 ****
      {
          List       *args;
          Const       *cons;
-         Node       *fcall;

          /* Pass given value, plus target typmod as an int4 constant */
          cons = makeConst(INT4OID,
--- 476,481 ----
***************
*** 523,541 ****
              args = lappend(args, cons);
          }

!         fcall = build_func_call(funcId, targetTypeId, args, cformat);
!
!         /*
!          * If the input is a constant, apply the length coercion
!          * function now instead of delaying to runtime.
!          *
!          * See the comments for the similar case in coerce_type.
!          */
!         if (node && IsA(node, Const) &&
!             !((Const *) node)->constisnull)
!             node = eval_const_expressions(fcall);
!         else
!             node = fcall;
      }

      return node;
--- 502,508 ----
              args = lappend(args, cons);
          }

!         node = build_func_call(funcId, targetTypeId, args, cformat);
      }

      return node;

Thanks Tom,

I thought that was something you were thinking about/putting on a list to do
later. As usual the speed of fixes being given is amazing.

I'll see if I can apply it later today or tomorrow, I need to move to 7.3.2 as
well so I can try it out then.


--
Nigel J. Andrews


On Wed, 9 Apr 2003, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > mydb=> select cast('truE'::text as boolean);
> > WARNING:  Error occurred while executing PL/pgSQL function text_to_bool
> > WARNING:  line 4 at select into variables
> > ERROR:  CopyQuerySnapshot: no snapshot has been set
>
> I've applied the attached patch to fix this.
>
>             regards, tom lane
>
> *** REL7_3/src/backend/parser/parse_coerce.c.orig    Thu Oct 24 18:09:00 2002
> --- REL7_3/src/backend/parser/parse_coerce.c    Wed Apr  9 22:47:57 2003
> ***************
> *** 226,251 ****
>                   result = (Node *) makeRelabelType(result, targetTypeId, -1,