Skip site navigation (1) Skip section navigation (2)

Re: starting on functions (with little succes)

From: "ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu>
To: Odysseus <bart(dot)pietercil(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: starting on functions (with little succes)
Date: 2011-07-06 16:19:47
Message-ID: 20110706161947.GA27687@staff-mud-56-27.rice.edu (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Daniel StaalDate: 2011-07-06 20:14:16
Subject: Best Practice for Address storage.
Previous:From: OdysseusDate: 2011-07-06 16:01:40
Subject: starting on functions (with little succes)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group