Re: starting on functions (with little succes)

On Wed, Jul 06, 2011 at 09:01:40AM -0700, Odysseus wrote:
> Hi List,
>
> we are converting from another sql db and I need some assistance on an error
> with this function:
> ---------------------------------------------------
> create FUNCTION createNewClient(clientName varchar, coreURL varchar,
> vs_ip varchar, vs_db varchar, vs_connstring varchar, vs_usr_pw varchar,
> lifespanUnitLV int2, lifespan int2 DEFAULT null) RETURNS void
>     AS $BODY$
> declare
>  aSerial uuid;
>  lifeSpanUnitPK bigint;
>
> begin
>     if lifespanUnitLV <> 4 and lifespan is null then
>         raise exception null_value_not_allowed using hint = 'Lifespan cannot be
> null for a lifespanUnitLV different from 4';
>     end if
>
>     set aSerial = select newuuid();
>
>     set lifeSpanUnitPK = select tbl_typelistvalues.pkid
>                         from tbl_typelistvalues
>                         join tbl_typelists on tbl_typelistvalues.fk_typelist =
> tbl_typelists.pkid
>                         where listvalue = lifespanUnitLV AND typelistname =
> 'token_lifespan_units';
>     if lifeSpanUnitPK is null then
>         raise exception null_value_not_allowed using hint = 'Illegal value for
> lifespanUnitLV';
>     end if
>     insert into
>
tbl_clients(vserver_userpw,vserver_ip,vserver_dbname,vserver_connectionstring,client_name,client_serial,client_core_url,token_lifespan,objptr_token_lifespan_unit)
>     values
> (vs_usr_pw,vs_ip,vs_db,vs_connstring,clientName,aSerial,coreURL,lifespan,lifeSpanUnitPtr);
>
>
> exception
>     when others then
>         raise ;
> end;
>     $BODY$
>     LANGUAGE sql
>     CALLED ON NULL INPUT
>     SECURITY INVOKER
>     IMMUTABLE;
> -----------------------------------------------------
>
> This is rejected by the parser with error:
>
> Error : ERROR:  syntax error at or near "uuid"
> LINE 5:  aSerial uuid;
>
> I am  sure this is something basic that I am missing and sure would like to
> get some guidance with this issue.
>
> tia,
>
> Bart
>

Does your database have a UUID type?

Ken