How to connect redis from postgres stored procedure.

If you want to use redis from postgres stored procedure. then there no any straigh forward command or function to do it.
but you can do it through c functions. postgres allow users to create c function that can be used for any purpose in postgres sql queries or in postgres stored procedure.

Redis is a database structure that stored data in memory. we can used redis as a database. redis stored data in key/value format and it stores data in different kind of structures according to users requirememnt. many user use redis as a datbase and many uses it for caching.

Hiredis is a c library that can be used to connect redis server from c language. All redis command can be used through hiredis in c language. So here we can create a function in c language who will communicate with redis.

You need to install hiredis , gcc compiler, postgres and redis server. you can create below function in any popular text editor.

redic_connect.c
————————————-


#include "postgres.h"
#include
#include "utils/array.h"
#include "fmgr.h"
#include "utils/geo_decls.h"
#include
#include
#include
#include "funcapi.h"
#include "assert.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

char *dup_pgtext(text *what);

char *dup_pgtext(text *what) {
size_t len = VARSIZE(what)-VARHDRSZ;
char *dup = palloc(len+1);
memcpy(dup, VARDATA(what), len);
dup[len] = '\0' ;
return dup;
}

/*
Belew function is used to set any string in redis server.
This function accept two arguments.
Both arguments will be passed in postgres stored procedure.
here we are accepting only one arguement PG_FUNCTION_ARGS.
and we wil fetch both arguments from this PG_FUNCTION_ARGS array.
*/

PG_FUNCTION_INFO_V1(set_str);
Datum
set_str(PG_FUNCTION_ARGS) {

//arguments are returned in text type, text type is a strucutre defined by postgres.
// so we will fetch char from text type.

char *arg1 = dup_pgtext(PG_GETARG_TEXT_P(0));

char *arg2 = dup_pgtext(PG_GETARG_TEXT_P(1));

if(check_pgtext(PG_GETARG_TEXT_P(0)) == 0) {
PG_RETURN_NULL();
}

if(check_pgtext(PG_GETARG_TEXT_P(1)) == 0) {
PG_RETURN_NULL();
}

text *err_str = PG_GETARG_TEXT_P(1);

redisContext *c;

redisReply *reply;
const char *hostname = "127.0.0.1";
int port = 6379;

struct timeval timeout = { 1, 500000 }; // 1.5 seconds
c = redisConnectWithTimeout(hostname, port, timeout);
if (c == NULL || c->err) {
if (c) {
redisFree(c);
} else {
}
exit(1);
}

reply = redisCommand(c,"SET %s %s", arg1, arg2);

arg1 = NULL;
arg2 = NULL;

if (reply != NULL && c->err) {
freeReplyObject(reply);
redisFree(c);
}

freeReplyObject(reply);
redisFree(c);

PG_RETURN_TEXT_P(err_str);

}

/*
below function isused to read a string value from redis server
this function accept only one argument and returs redis value.
*/

PG_FUNCTION_INFO_V1(read_str);

Datum
read_str(PG_FUNCTION_ARGS) {

char *arg1 = dup_pgtext(PG_GETARG_TEXT_P(0));

if(check_pgtext(PG_GETARG_TEXT_P(0)) == 0) {
PG_RETURN_NULL();
}

char *message;

redisContext *c;
redisReply *reply;
const char *hostname = "127.0.0.1";
int port = 6379;

c->reader->maxbuf = 0;

struct timeval timeout = { 1, 500000 }; // 1.5 seconds
c = redisConnectWithTimeout(hostname, port, timeout);
if (c == NULL || c->err) {
if (c) {
redisFree(c);
} else {
}
exit(1);
}

reply = redisCommand(c,"exists %s", arg1);

if(reply->integer == 1) {
reply = redisCommand(c,"GET %s",arg1);

message = reply->str;
text *r = copy_pgtext(message);
PG_RETURN_TEXT_P(r);
}else {
PG_RETURN_NULL();
}
}

/*
below function check the redis server for any key.
if key exists then it return 1.
this function accept one argements.
*/

PG_FUNCTION_INFO_V1(check_redis);

Datum
check_redis(PG_FUNCTION_ARGS) {

if(check_pgtext(PG_GETARG_TEXT_P(0)) == 0) {
PG_RETURN_NULL();
}

redisContext *c;
redisReply *reply;
Datum element;
bool isnull;
const char *hostname = "127.0.0.1";
int port = 6379;

struct timeval timeout = { 1, 500000 }; // 1.5 seconds
c = redisConnectWithTimeout(hostname, port, timeout);
if (c == NULL || c->err) {
if (c) {
redisFree(c);
} else {
}
exit(1);
}

char *arg1 = dup_pgtext(PG_GETARG_TEXT_P(0));

reply = redisCommand(c,"exists %s", arg1);

int res;

res = reply->integer;

freeReplyObject(reply);

redisFree(c);

PG_RETURN_INT64(res);
}

/*
Below function delete any key from redis server.
this function accept one argument.
*/

PG_FUNCTION_INFO_V1(delete_redis);

Datum
delete_redis(PG_FUNCTION_ARGS) {

if(check_pgtext(PG_GETARG_TEXT_P(0)) == 0) {
PG_RETURN_NULL();
}

redisContext *c;
redisReply *reply;
Datum element;
bool isnull;
const char *hostname = "127.0.0.1";
int port = 6379;

struct timeval timeout = { 1, 500000 }; // 1.5 seconds
c = redisConnectWithTimeout(hostname, port, timeout);
if (c == NULL || c->err) {
if (c) {
redisFree(c);
} else {
}
exit(1);
}

char *arg1 = dup_pgtext(PG_GETARG_TEXT_P(0));

reply = redisCommand(c,"del %s", arg1);

int res;

res = reply->integer;

freeReplyObject(reply);

redisFree(c);

PG_RETURN_NULL();

}

There is no file for download, you can copy paste code from here.

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

eleven + two =