Simple functions with return expression

Moderator: NorbertKrupa

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Simple functions with return expression

Post by nnani » Mon Mar 11, 2013 11:36 am

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Simple functions with return expression

Post by JimKnicely » Mon Mar 11, 2013 2:06 pm

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.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Simple functions with return expression

Post by id10t » Mon Mar 11, 2013 11:14 pm

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)

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Simple functions with return expression

Post by nnani » Tue Mar 12, 2013 3:13 pm

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.
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Simple functions with return expression

Post by id10t » Wed Mar 13, 2013 12:13 am

Hi!

>> Any clues on this.
Yes. Mistake in she-bang, should be #!/bin/bash and not #! bin/bash. She-bang should start from root.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Simple functions with return expression

Post by nnani » Wed Mar 13, 2013 9:56 pm

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?
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Simple functions with return expression

Post by id10t » Wed Mar 13, 2013 10:29 pm

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).

Post Reply

Return to “Vertica SQL”