Page 1 of 2
Simple functions with return expression
Posted: Mon Mar 11, 2013 11:36 am
by nnani
Hello All,
It may sound the simplest questions of all time.
I am trying to create some functions in vertica.
Function name - Total_size_in_MB
Function operation - This function should be able to tell us the total size occupied by the Data in your database.
Code: Select all
CREATE FUNCTION total_size_in_mb() return decimal
as begin
RETURN(select CEIL(sum(used_bytes)/1024/1024) as total_size_in_MB from v_monitor.storage_containers);
end;
ROLLBACK 4257: Only simple "RETURN expression" is allowed
While I am not giving any input arguments here. The SQL is giving an error as above.
Can somebody please explain this behaviour.
Re: Simple functions with return expression
Posted: Mon Mar 11, 2013 2:06 pm
by JimKnicely
We cannot use SELECT statements in functions...
viewtopic.php?f=6&t=118&p=2566&hilit=function#p2566
You'll have to do that as an
external procedure.
Re: Simple functions with return expression
Posted: Mon Mar 11, 2013 11:14 pm
by id10t
Hi!
Question is a function or question is a data?
this can help?
Code: Select all
sandbox=> \set db_size 'select ceil(sum(used_bytes) / 1024^2) as db_size_mb from v_monitor.storage_containers;'
sandbox=> :db_size
db_size_mb
------------
4
(1 row)
Re: Simple functions with return expression
Posted: Tue Mar 12, 2013 3:13 pm
by nnani
Thanks skwa,
The workaround by skwa was perfect.
I want to make a
External procedure for this, at which I am getting some error.
My script is total_size.sh
Code: Select all
#! bin/bash
vsql -c "select CEIL(sum(used_bytes)/1024/1024) as total_size_in_MB from v_monitor.storage_containers;"
exit 0
When running the script it runs perfectly.
While Creating
procedure
1. Installed the
procedure using admintools perfectly
2. Created the
Procedure
Code: Select all
create procedure total_size_in_MB() as 'total_size.sh' language 'external' user 'dbadmin'
3. The
procedure was created successfully and then I checked the user_procedures system table for its existence and it was there.
4. While executing this query
Code: Select all
dbadmin=> select total_size_in_MB();
INFO 4427: Procedure reported:
Could not execute procedure, errno=2
ERROR 4424: Procedure execution error: exit status=1
dbadmin=>
I am confused . Any clues on this.
Re: Simple functions with return expression
Posted: Wed Mar 13, 2013 12:13 am
by id10t
Hi!
>> Any clues on this.
Yes. Mistake in she-bang, should be #!/bin/bash and not #! bin/bash. She-bang should start from root.
Re: Simple functions with return expression
Posted: Wed Mar 13, 2013 9:56 pm
by nnani
OK, I rectified it and checked the script, its working fine.
Dropped the previous
procedure and created a new one
Code: Select all
dbadmin=> select total_siz();
total_siz
-----------
0
(1 row)
dbadmin=>
Is there anything wrong with my
external procedure? What are the languages. we can write the
external procedures in?
Re: Simple functions with return expression
Posted: Wed Mar 13, 2013 10:29 pm
by id10t
Hi!
>> is there anything wrong with my external procedure?
No, everything is ok.
PS
External procedures(EP) do not returns an answer to Vertica, only a return code of finished procedure - and your procedure finished with success(everything is ok, don't see any problem).
You can NOT use in answer of EP (to see - yes, to use - no). To see an answer of EP use in `echo`, Vertica will print it.
Goal of EP - to run external scripts inside of Vertica clients(vsql for example or dbeaver or whatever) without exiting to system or script interpreter, nothing else (EP it's not SP, and you think that are - ok).