Answer the question
In order to leave comments, you need to log in
How to translate array and jsonb object returned by postgres function to string list and c# dictionary?
I have a function that returns from a database table columns represented by text[] and jsonb types:
CREATE OR REPLACE FUNCTION public.getValues(
medicamentID uuid)
RETURNS TABLE("Compound" text[], "DoseInstruction" jsonb)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
RETURN QUERY SELECT "Medicaments"."Compound", "Medicaments"."DoseInstruction"
FROM "Medicaments"
WHERE "ID" = medicamentID;
END
$BODY$;
Npgsql.NpgsqlCommand pgcom1 = new Npgsql.NpgsqlCommand(@"getvalues", pgcon);
pgcom1.CommandType = CommandType.StoredProcedure;
pgcom1.Parameters.AddWithValue("medicamentid", Guid.Parse(MedicamentsID.Text));
Npgsql.NpgsqlDataReader pgreader1 = pgcom1.ExecuteReader();
List<string> compound = new List<string>();
Dictionary<string, string> doseInstruction = new Dictionary<string, string>();
if (pgreader1.Read())
{
compound = (from IDataRecord r in pgreader1
select (string)r["Compound"]
).ToList();
doseInstruction = JsonConvert.DeserializeObject<Dictionary<string, string>>(pgreader1.GetString(1));
}
pgreader1.Close();
pgcon.Close();
doseInstruction = JsonConvert.DeserializeObject<Dictionary<string, string>>(pgreader1.GetString(1));
SELECT getValues('280ca185-3701-432f-86d0-1209db3f4123');
RESULT:
("{Препарат1,Препарат2}","{""Дети"": ""1"", ""Взрослые"": ""2""}")
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question