Simple functions with return expression

Moderator: NorbertKrupa

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 » Thu Mar 14, 2013 6:44 am

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.
So, I modified my script to

Code: Select all

#!/bin/bash

value=`vsql -c "select CEIL(sum(used_bytes)/1024/1024) as total_size_in_MB from v_monitor.storage_containers;"`
echo $value

exit 0

Code: Select all

[dbadmin@node1 ~]$ sh total_size.sh
total_size_in_MB ------------------ 284 (1 row)
[dbadmin@node1 ~]$
So the script is working ok. Now I again dropped the previous procedure and installed a new one
Created the procedure and tried running it.

Code: Select all


dbadmin=> select total_size();
INFO 4427:  Procedure reported:


 total_size
------------
          0
(1 row)

dbadmin=>
How do I print my result using Vertica.
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 » Thu Mar 14, 2013 7:41 am

Hi!

Now I don't know what a reason of your problem, coz' I checked on 2 systems: 6.1 and 6.1.1, everything works.
Check your environment.

Code: Select all

daniel@synapse:~$ cat /vertica/sandbox/procedures/db_size.sh
#!/bin/bash

size=`/opt/vertica/bin/vsql -XnqAtc "
select (sum(used_bytes)/1024^2)::numeric(18,2) 
  from v_monitor.storage_containers;"`

echo $size

exit 0

Code: Select all

$ /vertica/sandbox/procedures/db_size.sh
3.78

Code: Select all

sandbox=> select db_size();
INFO 4427:  Procedure reported:
3.78

 db_size 
---------
       0
(1 row)
UPDATE

Result of your script on my system (I see output):

Code: Select all

daniel=> select db_size();
INFO 4427:  Procedure reported:
total_size_in_MB ------------------ 4 (1 row)

 db_size 
---------
       0
(1 row)

Post Reply

Return to “Vertica SQL”