How to access the database and quries through the procedures

Moderator: NorbertKrupa

Post Reply
malargopal
Newbie
Newbie
Posts: 23
Joined: Mon Feb 04, 2013 10:54 am

How to access the database and quries through the procedures

Post by malargopal » Wed Jun 26, 2013 7:56 am

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

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

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

Post by nnani » Wed Jun 26, 2013 9:28 am

Hello,

This topic can solve your doubt.

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

Hope this helps :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

malargopal
Newbie
Newbie
Posts: 23
Joined: Mon Feb 04, 2013 10:54 am

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

Post by malargopal » Wed Jun 26, 2013 10:57 am

Thanks :)

This post helped to acheive my goal.

banurajadurai
Newbie
Newbie
Posts: 21
Joined: Tue Aug 27, 2013 10:31 am

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

Post by banurajadurai » Wed Sep 11, 2013 11:07 am

Hi

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

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

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

Post by JimKnicely » Wed Sep 11, 2013 12:39 pm

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

Image

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

Post Reply

Return to “Vertica External Procedures”