Thread: new type question

I tried to create a new type with input and output functions in plpgsql and
it didn't work.
The error I got is there is no type uint2.
Is this because plpgsql does not allow you to create input/output fuctions?
It is a very simple function, so I didn't want to do it in C.
Is there a reason that it only works in C and not plpgsql?

I was expecting a message like:
NOTICE:  type "uint2" is not yet defined
DETAIL:  Creating a shell type definition.

but instead I got:
ERROR:  type "uint2" does not exist

create function uint_in(val cstring) returns uint2 as
$$
declare thisval int4;
begin
 thisval=val::int4
 if thisval between 0 and 65535 then
  return (thisval-32768)::int2;
 else
  return 0;
 end if;
end
$$ language 'plpgsql';

On Sun, Oct 16, 2005 at 11:03:52AM +0200, Sim Zacks wrote:
> I tried to create a new type with input and output functions in plpgsql and
> it didn't work.
> The error I got is there is no type uint2.
> Is this because plpgsql does not allow you to create input/output fuctions?
> It is a very simple function, so I didn't want to do it in C.
> Is there a reason that it only works in C and not plpgsql?

Unfortunatly, I don't think type input/output functions can be written
in anything other than C. IIRC, no other PL understands the cstring type.

As for why, well, because nobody has done the legwork yet. Oh yes, type
input/output functions are also called at various points, including
during parsing where there is no current snapshot. Whether the PLs are
affected by this I don't know, but you cannot do SPI at that point.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Sun, Oct 16, 2005 at 11:03:52AM +0200, Sim Zacks wrote:
> create function uint_in(val cstring) returns uint2 as
> $$
> declare thisval int4;
> begin
>  thisval=val::int4
>  if thisval between 0 and 65535 then
>   return (thisval-32768)::int2;
>  else
>   return 0;
>  end if;
> end
> $$ language 'plpgsql';

On a side note, do you really want to punt to 0 when an invalid value
comes it? That sounds like something MySQL would do... ISTM you should
throw an error.

Also, you could have written that as a pure SQL function, which would
have been faster (assuming you could use something other than C for
this).
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

I was needing something similar last week, not to throw an error,
but to catch an error when 99% of my column's data is real, but some
is not (e.g. '1.2-1.4' or '>32.7').  I wanted to do it in pure
SQL, but settled on this.  Is there a way to do this in pure
SQL (so it will be faster)?

Declare x real;
Begin
   x = cast($1 as real);
   Return x;
Exception
   When OTHERS Then
     Return NULL;
End;

Thanks,
TJ

> On a side note, do you really want to punt to 0 when an invalid value
> comes it? That sounds like something MySQL would do... ISTM you should
> throw an error.
>
> Also, you could have written that as a pure SQL function, which would
> have been faster (assuming you could use something other than C for
> this).
>>create function uint_in(val cstring) returns uint2 as
>>> $$
>>> declare thisval int4;
>>> begin
>>>  thisval=val::int4
>>>  if thisval between 0 and 65535 then
>>>   return (thisval-32768)::int2;
>>>  else
>>>   return 0;
>>>  end if;
>>> end
>>> $$ language 'plpgsql';
>
>

Hrm, I thought there was something equivalent to an is_number()
function, which would have made this easy:

CASE WHEN is_number(x) THEN x ELSE NULL END;

But I can't seem to find one. Is there a historic reason such functions
don't exist?

On Mon, Oct 17, 2005 at 01:05:17PM -0700, TJ O'Donnell wrote:
> I was needing something similar last week, not to throw an error,
> but to catch an error when 99% of my column's data is real, but some
> is not (e.g. '1.2-1.4' or '>32.7').  I wanted to do it in pure
> SQL, but settled on this.  Is there a way to do this in pure
> SQL (so it will be faster)?
>
> Declare x real;
> Begin
>   x = cast($1 as real);
>   Return x;
> Exception
>   When OTHERS Then
>     Return NULL;
> End;
>
> Thanks,
> TJ
>
> >On a side note, do you really want to punt to 0 when an invalid value
> >comes it? That sounds like something MySQL would do... ISTM you should
> >throw an error.
> >
> >Also, you could have written that as a pure SQL function, which would
> >have been faster (assuming you could use something other than C for
> >this).
> >>create function uint_in(val cstring) returns uint2 as
> >>>$$
> >>>declare thisval int4;
> >>>begin
> >>> thisval=val::int4
> >>> if thisval between 0 and 65535 then
> >>>  return (thisval-32768)::int2;
> >>> else
> >>>  return 0;
> >>> end if;
> >>>end
> >>>$$ language 'plpgsql';
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461