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