Page 1 of 1

How to access the database and quries through the procedures

Posted: Wed Jun 26, 2013 7:56 am
by malargopal
HI all,

How to access the database and quries through the procedures.

The following one i tried to access , but I couldnt get the exact out put.

ShellScript:

#!/bin/bash
vsql -c "select MFR_PART_KEY from vmro.DIM_ATA limit 10;"
exit 0


Creating procedure:

dbadmin=> create procedure query(arg1 varchar) AS 'query1.sh' LANGUAGE 'external' USER 'dbadmin';
CREATE PROCEDURE


Executing procedure:

dbadmin=> select query('vmro.dimata');
query
-------
0
(1 row)

Please correct me where i went wrong .

Thanks,
Malar

Re: How to access the database and quries through the proced

Posted: Wed Jun 26, 2013 9:28 am
by nnani
Hello,

This topic can solve your doubt.

http://www.vertica-forums.com/viewtopic ... DURE#p2777

Hope this helps :)

Re: How to access the database and quries through the proced

Posted: Wed Jun 26, 2013 10:57 am
by malargopal
Thanks :)

This post helped to acheive my goal.

Re: How to access the database and quries through the proced

Posted: Wed Sep 11, 2013 11:07 am
by banurajadurai
Hi

What is the command used to view the external procedures created ?

Re: How to access the database and quries through the proced

Posted: Wed Sep 11, 2013 12:39 pm
by JimKnicely
Query the V_CATALOG.USER_PROCEDURES table:

Code: Select all

dbadmin=> SELECT * FROM v_catalog.user_procedures;
 procedure_name | procedure_arguments | schema_name
----------------+---------------------+-------------
(0 rows)

dbadmin=> CREATE PROCEDURE public.truncate_table(arg1 VARCHAR) AS 'truncate_table.sh' LANGUAGE 'external' USER 'dbadmin';
CREATE PROCEDURE

dbadmin=> SELECT * FROM v_catalog.user_procedures;
 procedure_name | procedure_arguments | schema_name
----------------+---------------------+-------------
 truncate_table | arg1 Varchar        | public
(1 row)