Neyolnotsa.PostgreSQL 0.1.4

Neyolnotsa PostgreSQL

• Micro ORM to PostgreSQL
• Net 8
• PostgreSQL 16

Example of C#:

    using Neyolnotsa.PostgreSQL;
    using Npgsql;
 

    string cs = "Server=<server>;Port=5432;Database=<db>;User Id=postgres;Password=<password>;";
    Response_JSON rsp;

    using (var db = new NpgsqlConnection(cs))
    {
        Request_JSP rqs = new Request_JSP()
        {
            procedure = "json_Item",
            inputParams = new
            {
                Function = 1,
                Id_item = 1
            }
        };

        rsp = db.ExecuteFNJ(rqs);
        Console.WriteLine(rsp.ToJson(prettyPrint: true));
    }

Example of a function in PostgreSQL:

CREATE OR REPLACE FUNCTION json_Item(json_params CHARACTER VARYING = '')
RETURNS JSON
AS
$$
DECLARE 
	str_json 		JSON;
	func 			INTEGER;
	Id_Item 		INTEGER;
	json_output		JSON;
BEGIN

	IF json_params = '' THEN
		RETURN json_Output(-1, 'No parameters received.', false);
	END IF;

 	SELECT 	COALESCE((json_params::jsonb ->> 'Id_Item')::INTEGER, 0),
	 		COALESCE((json_params::jsonb ->> 'Function')::INTEGER, 0)
	INTO	Id_Item, func

	IF (func IS NULL) THEN
		RETURN json_Output(-1, 'The function does not exist.', false);
	END IF;

	-- Item By Id
	IF (func = 1) THEN

		SELECT json_Output(1, 'Ok', true, json_agg(row_to_json(T0)))
	    INTO str_json
	    FROM (
	        SELECT *
	        FROM "Items"
	        WHERE "Id_Item" = Id_Item
	    ) T0;

		RETURN str_json;
	END IF;

	/* 
	IF (func = 2) THEN
		--... other function
	END IF;
	*/

END;
$$ 
LANGUAGE plpgsql;

json_output is a function that returns a JSON object with the following structure:

CREATE OR REPLACE FUNCTION public.json_output(
	code 		INTEGER,
	msg 		CHARACTER VARYING,
	rst 		BOOLEAN,
	json_data 	JSON DEFAULT '{}'::JSON
)
    RETURNS	json
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE 
	str_json JSON;
BEGIN

	SELECT json_build_object(
				'success', rst,
				'code', code,
				'message', msg,
				'data', json_data
	)
	INTO str_json;

	RETURN str_json;
END;
$BODY$;