Hello All,
We are using Postgresql external DB for our project. Our outsystems environment is in AWS cloud and Postgresql instance is also in AWS cloud. We have no issues in connecting to database and fetching the data from postgresql db tables. Also we have no issues in calling a scalar functions in postgresql.
The issue we are facing is : we are not able to get data from postgresql table functions (Function returning table as result set).
Here is the screenshot of the error message.
Please let us know if anyone can fetch data from postgresql table functions.
Here is the function code :
We are able to resolve this issue by writing an extension with following code.
Showing a sample code
///
/// Get Offerings Progress
public void MssGetOfferingsProgress(out RLOfferingProgressRecordRecordList ssOfferingsProgressList) {
ssOfferingsProgressList = new RLOfferingProgressRecordRecordList();
// Retrieve the DatabaseProvider for the external database.
// You need to have a Database Connection called "PostgresqlConnection" configured
// in Service Center.
//DatabaseProvider dbaProvider = DatabaseAccess.ForExternalDatabase("PostgresqlConnection ");
using (CommittableTransaction commitableTransaction = dbaProvider.GetCommittableTransaction())
{
using (Command cmd = commitableTransaction.CreateCommand("select offering_id, offering_name , status , stage , progress from core.fn_dashboard_get_offerings_progress()"))
// Results are read using a standard IDataReader object
using (IDataReader reader = cmd.ExecuteReader())
while (reader.Read())
STOfferingProgressRecordStructure sTOfferingProgressRecord = new STOfferingProgressRecordStructure();
sTOfferingProgressRecord.ssoffering_id = reader.GetInt32(0);
sTOfferingProgressRecord.ssoffering_name = reader.GetString(1);
sTOfferingProgressRecord.ssstatus=reader.GetString(2);
sTOfferingProgressRecord.ssstage=reader.GetString(3);
sTOfferingProgressRecord.ssprogress = reader.GetInt32(4);
ssOfferingsProgressList.Append(sTOfferingProgressRecord);
Console.WriteLine(reader.GetInt32(0));
}
reader.Close();
} // MssGetOfferingsProgress
Hello,
isn't "public" a reserved "keyword"?
Have you tried to enclose it with 'quotes'?
Kind regards, Markus
It is not a reserved word in postgresql and it is just the name of the schema.
The same statement works perfectly in pgadmin client tool and return the data : select * from public.functionname()
Also i tried removing it and adding quotes but nothing seems to fetch data from DB.
Anyways thanks for your response.