Thread: starting on functions (with little succes)
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
^
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/starting-on-functions-with-little-succes-tp4557235p4557235.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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
Hi Ken, changing the type of aSerial to varchar gives: Error : ERROR: syntax error at or near "varchar" LINE 6: aSerial varchar; -- View this message in context: http://postgresql.1045698.n5.nabble.com/starting-on-functions-with-little-succes-tp4557235p4558828.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
ok, one error fixed: do not combine declare with language 'sql'. When using
declare use 'plpgsql'
now this is accepted:
create FUNCTION createNewClient(clientName varchar, coreURL varchar,vs_ip
varchar, vs_db varchar, vs_connstring varchar, vs_usr_pw
varchar,lifespanUnitLV varchar, lifespan int2 DEFAULT null) RETURNS void
AS $BODY$
declare
aSerial uuid;
lifeSpanUnitPK bigint;
begin
SELECT INTO aSerial
newuuid();
select into lifeSpanUnitPK
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';
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 plpgsql
CALLED ON NULL INPUT
SECURITY INVOKER
IMMUTABLE;
--------------
Problem is not solved for this:
after BEGIN i would like to use this if then statement:
-----------------
if lifespanUnitLV = '4' then
end if
----------------
This returns an error: Error : ERROR: syntax error at or near "SELECT"
LINE 14: SELECT INTO aSerial
^
It must be something with the IF itself.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/starting-on-functions-with-little-succes-tp4557235p4559069.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
of course hitting post made me realize what the error was. EVERY block within pgplsql must end with a ';'. (can I say : old habits die hard...?) Sorry for the noice. -- View this message in context: http://postgresql.1045698.n5.nabble.com/starting-on-functions-with-little-succes-tp4557235p4559083.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.