0

I've a stored procedure written in PL/postgresql, which returns results in json format. I want to convert that stored procedure into postgres Compiled (C) Language Function. Now the problem is if I compile sample PG_FUNCTION_INFO_V1 fuction , the function can be called from the shell but cannot be done for stored procedure? Is there any way to do this? Any examples?

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
#include <libpq-fe.h>
#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(version);
Datum
version(PG_FUNCTION_ARGS){

PGconn *conn = PQconnectdb("host=localhost user=postgres dbname=state port=5432 password=fpdb");
PGresult *res = PQexec(conn, "select count(*) ucount from wo_status"); 

for (; ;)
{
printf("\t  State Health DB\n");
printf("\t1.\t generate_default_schedule_for_technician\n"); //generatedefaultschedulefortechnician (integer)
printf("\t2.\t get_fp_claimsdata\n"); //get_fp_claimsdata (integer, integer)
printf("\t3.\t get_fp_dashboard\n"); //get_fp_dashboard('',0)
printf("\t4.\t get_userreports\n"); //get_userreports (integer, integer)
printf("\t5.\t get_support_details_forprovider\n"); //getsupportdetailsforprovider (integer)
printf("\t6.\t Exit\n");
scanf("%d",&procedure_swtich);

switch(procedure_swtich){
    case 1 : res = PQexec(conn, "select generatedefaultschedulefortechnician(0)");
    break;
    case 2 : res = PQexec(conn, "select get_fp_claimsdata(1,1)");
    break;
    case 3 : res = PQexec(conn, "select get_fp_dashboard('',0)");
    break;
    case 4 : res = PQexec(conn, "select get_userreports(1,1)");
    break;
    case 5 : res = PQexec(conn, "select getsupportdetailsforprovider(0)");
    break;
    case 6 : exit(1);
    default : printf("\tEnter the choice\n");
}   
int32 rows = PQntuples(res);

PQclear(res);
PQfinish(conn);

int32 arg1 = PG_GETARG_INT32(0);
int32 arg2 = PG_GETARG_INT32(1);
PG_RETURN_INT32(arg1 + arg2);

}

  • Please edit your question and add the original PL/pgSQL function, your C function and the error you get when calling the function. Did you try to convert your function to a language sql function? They are very often faster than PL/pgSQL (if they can be re-written) – a_horse_with_no_name Dec 21 '19 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.