how to parse response out of postgresql ? how to better use npgsql.

can u explain the structure of the string result you got here?
i can "serialize" this string into an object if you could tell me what is the structure.
cheers

hello sedgwick,

i attached my questions as i could post in here.......problems with the square braquets....the page wanted me to close "\[i\]"...as if it was xml...
quetsion.txt

why the function doesn't return table as a result?

that way you can use NpgsqlDataAdapter to fill a .net dataset:

var con= new Npgsql.NpgsqlConnection(strConstring);

con.Open();

var command = new Npgsql.NpgsqlCommand("MyFunction", con);
command.CommandType = System.Data.CommandType.StoredProcedure;

var adapter= new Npgsql.NpgsqlDataAdapter(command);
var ds = new System.Data.DataSet();
adapter.Fill(ds);

Select allOpen in new window

sedwick,

i'd prefer to return a table, i'm not an expert :/ i had hard time to create this function.
would you have a possibility to return a table ?

i guess it would be easier for me to recover in the datatable of .net.

i'm trying the possibility you send.

thank you..

and please if i you have a possibility on how to put this in a table , i 'll be happy to use the possibility in my postgresql functions.


toshi

hello sedwick.

is it normal that you create a temporary TABLE "export_result_table" and then you fill "temp_payroll" ?

this is i guess the best metthod by creating a temporary table, fill it and return it.
but i can not succeed in my code:

here's my function as you can see, i return the temporary table "export_result_table" :

the problem with the following function is that i  receveive the following errors:
if i put a return next....another error comes up....
thank you for further help....



-------------- ERRORS --------------------------
CREATE FUNCTION
psql:c:/coderie/plpgsql/eplanDigged/exporting_account_conso/export_conso_account_best.sql:322: ERROR:  RETURN cannot have a parameter in function
 returning set
LINE 78:  RETURN export_result_table;
                 ^
HINT:  Use RETURN NEXT or RETURN QUERY.
psql:c:/coderie/plpgsql/eplanDigged/exporting_account_conso/export_conso_account_best.sql:342: ERROR:  function exporter_consommation_tous_compte
() does not exist
LINE 12: SELECT * FROM exporter_consommation_tous_compte();
                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
lavigny_1175=#

---------------------  FUNCTION -----------------------
CREATE OR REPLACE FUNCTION exporter_consomation_tous_compte() RETURNS setof export_result AS
$$
DECLARE
      row_account user_person%ROWTYPE;
      result export_result\[\];
      i integer := 0;
      aRow export_result;
BEGIN

      -- temp table to be returned
      CREATE TEMPORARY TABLE export_result_table(
            account_no int,
            lastname character varying(100),
            firstname character varying(100),
            address character varying(100),
            address_no character varying(100),
            zip character varying(200),       
            city character varying(100),
            reference character varying(100),
            rf_tags_id int[],
            ecowaste_units integer,
            masse_en_kg real,
            cout_absolut real,
            cout_arrondi real
      ) on commit drop;
            


      FOR row_account IN SELECT * FROM user_person WHERE user_person_id < 1000000
      LOOP
            -- RAISE NOTICE '%', exporter_consommation_compte(row_account.user_person_id);
            -- result\[i\] = exporter_consommation_compte(row_account.user_person_id);
            aRow = exporter_consommation_compte(row_account.user_person_id);
            -- INSERT INTO  temp_payroll_val VALUES(
             INSERT INTO  export_result_table VALUES(
                        aRow.account_no,
                        aRow.lastname,
                        aRow.firstname,
                        aRow.address ,
                        aRow.address_no,
                        aRow.zip,       
                        aRow.city,
                        aRow.reference,
                        aRow.rf_tags_id ,
                        aRow.ecowaste_units,
                        aRow.masse_en_kg ,
                        aRow.cout_absolut,
                        aRow.cout_arrondi
            );
                  
            -- i := i+1;
            -- RETURN NEXT export_result_table ;
      END LOOP;
      -- RETURN result;
      -- RETURN temp_payroll_val;
      RETURN export_result_table;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM exporter_consommation_tous_compte();



toshi_

here's a vb.net class which will parse your string result from the function.
to parse the string result simply call it like this:

Dim result = ExportResultData.Create("result_data_from_func")

Imports System.ComponentModel
Imports System.Linq


Public Class ExportResultData
    Public Shared Function Create(raw_data As String) As IEnumerable(Of ExportResultData)
        Dim dataResult = New List(Of ExportResultData)()

        Dim tokens = raw_data.Replace("\", String.Empty).Replace("""", String.Empty).Split(New String() {Environment.NewLine, "(", ")"}, StringSplitOptions.RemoveEmptyEntries)
        tokens = tokens.Where(Function(n, i) (i Mod 2 <> 0)).ToArray()
        For Each token As String In tokens
            Dim values = token.Split(New Char() {"{"c, "}"c}, StringSplitOptions.RemoveEmptyEntries)
            Dim list = New List(Of String)()
            If values.Length = 1 Then
                list.AddRange(values(0).Split(New Char() {","c}, StringSplitOptions.None))
            Else
                Dim vals = values(0).Split(New Char() {","c}, StringSplitOptions.None)
                list.AddRange(vals.Take(vals.Length - 1))
                list.Add(values(1))
                vals = values(2).Split(New Char() {","c}, StringSplitOptions.None)
                list.AddRange(vals.Skip(1))
            End If

            dataResult.Add(New ExportResultData() With { _
              .account_no = TryParse(Of Integer)("account_no", list(0)), _
              .lastname = list(1), _
              .firstname = list(2), _
              .address = list(3), _
              .address_no = list(4), _
              .zip = list(5), _
              .city = list(6), _
              .reference = list(7), _
              .rf_tags_id = TryParseArray(Of Integer)("rf_tags_id", list(8)), _
              .ecowaste_units = TryParse(Of Integer)("ecowaste_units", list(9)), _
              .masse_en_kg = TryParse(Of Decimal)("masse_en_kg", list(10)), _
              .cout_absolut = TryParse(Of Decimal)("cout_absolut", list(11)), _
              .cout_arrondi = TryParse(Of Decimal)("cout_arrondi", list(12)) _
            })
        Next

        Return dataResult
    End Function

    Public Property account_no() As System.Nullable(Of Integer)
        Get
            Return m_account_no
        End Get
        Private Set(value As System.Nullable(Of Integer))
            m_account_no = value
        End Set
    End Property
    Private m_account_no As System.Nullable(Of Integer)
    Public Property lastname() As String
        Get
            Return m_lastname
        End Get
        Private Set(value As String)
            m_lastname = value
        End Set
    End Property
    Private m_lastname As String
    Public Property firstname() As String
        Get
            Return m_firstname
        End Get
        Private Set(value As String)
            m_firstname = value
        End Set
    End Property
    Private m_firstname As String
    Public Property address() As String
        Get
            Return m_address
        End Get
        Private Set(value As String)
            m_address = value
        End Set
    End Property
    Private m_address As String
    Public Property address_no() As String
        Get
            Return m_address_no
        End Get
        Private Set(value As String)
            m_address_no = value
        End Set
    End Property
    Private m_address_no As String
    Public Property zip() As String
        Get
            Return m_zip
        End Get
        Private Set(value As String)
            m_zip = value
        End Set
    End Property
    Private m_zip As String
    Public Property city() As String
        Get
            Return m_city
        End Get
        Private Set(value As String)
            m_city = value
        End Set
    End Property
    Private m_city As String
    Public Property reference() As String
        Get
            Return m_reference
        End Get
        Private Set(value As String)
            m_reference = value
        End Set
    End Property
    Private m_reference As String
    Public Property rf_tags_id() As IEnumerable(Of Integer)
        Get
            Return m_rf_tags_id
        End Get
        Private Set(value As IEnumerable(Of Integer))
            m_rf_tags_id = value
        End Set
    End Property
    Private m_rf_tags_id As IEnumerable(Of Integer)
    Public Property ecowaste_units() As System.Nullable(Of Integer)
        Get
            Return m_ecowaste_units
        End Get
        Private Set(value As System.Nullable(Of Integer))
            m_ecowaste_units = value
        End Set
    End Property
    Private m_ecowaste_units As System.Nullable(Of Integer)
    Public Property masse_en_kg() As System.Nullable(Of Decimal)
        Get
            Return m_masse_en_kg
        End Get
        Private Set(value As System.Nullable(Of Decimal))
            m_masse_en_kg = value
        End Set
    End Property
    Private m_masse_en_kg As System.Nullable(Of Decimal)
    Public Property cout_absolut() As System.Nullable(Of Decimal)
        Get
            Return m_cout_absolut
        End Get
        Private Set(value As System.Nullable(Of Decimal))
            m_cout_absolut = value
        End Set
    End Property
    Private m_cout_absolut As System.Nullable(Of Decimal)
    Public Property cout_arrondi() As System.Nullable(Of Decimal)
        Get
            Return m_cout_arrondi
        End Get
        Private Set(value As System.Nullable(Of Decimal))
            m_cout_arrondi = value
        End Set
    End Property
    Private m_cout_arrondi As System.Nullable(Of Decimal)

    Private Shared Function TryParseArray(Of T As Structure)(field_name As String, input As String) As IEnumerable(Of T)
        If String.IsNullOrEmpty(input) Then
            Return Nothing
        End If

        Return input.Split(New Char() {","c}, StringSplitOptions.RemoveEmptyEntries).[Select](Function(n)
                                                                                                  Return TryParse(Of T)(field_name, n).Value

                                                                                              End Function)
    End Function

    Private Shared Function TryParse(Of T As Structure)(field_name As String, input As String) As System.Nullable(Of T)
        If String.IsNullOrEmpty(input) Then
            Return New Nullable(Of T)()
        End If

        Try
            Return DirectCast(TypeDescriptor.GetConverter(GetType(T)).ConvertFromString(input), T)
        Catch ex As Exception
            Throw New Exception(String.Format("Could not convert input [{0}] for field {1} typeof {2}", input, field_name, GetType(T)), ex)
        End Try
    End Function
End Class

Select allOpen in new window

the file that i'm reading contains the string result from your question:

"{\"(10004,Aeby,Aloys,\\\"Rte du Vignoble\\\",33,1175,lala,\\\"\\\",\\\"{1,2}\\\",968,48.4000015,26.6200008,0)\",\"(10005,Aeby,\\\"Andr¿ Christian\\\",\\\"Route de St-Livres\\\",7,1175,lala,AEBYAN000,\\\"{3,4}\\\",1466,73.3000031,40.3150024,0)\",\"(10006,Aeby,Freddy,\\\"Route du Moulin Martinet\\\",11,1175,lala,AEBYFR000,,,,,)\",\"(10009,Allemand,\\\"Pierre Ambroise\\\",\\\"Rte du Vignoble\\\",3,1175,Lavigny,ALLEPI000,\\\"{7,8}\\\",3590,179.5,98.7249985,0)\"}"

you should pass the result from your DB function.

hello sedgwick

first, thank you so much for your help !!! thank you for the possibility to parse the string....of course as i'm having a problem in returning the temporary table...and you are - as i can see - a savy sql coder so you give me the solution from where i am stuck which is perfect

if you dont mind, i'd like jto pass the function  with the return of the temporary table, it's going to be the solution that i guess i'll be using the most, as it's clean.
so far i've been trying but i dont succeed to receive the table as a return.

questions:
my type's name is export_result and the table is export_result_table, both have exactly same columns, same name, same type. When i am in the loop "exporter_consomation_tous_compte()" and i'm inserting into export_result_table, am i supposed to someting else ? because with a "RETURN export_result_table" which is the temporary table, i receive the following error:


----------------------   ERROR ----------------------------------------
psql:c:/coderie/plpgsql/eplanDigged/exporting_account_conso/export_conso_account_best.sql:322: ERROR:  RETURN cannot have a parameter in function
 returning set
LINE 78:  RETURN export_result_table;
                 ^
HINT:  Use RETURN NEXT or RETURN QUERY.
psql:c:/coderie/plpgsql/eplanDigged/exporting_account_conso/export_conso_account_best.sql:341: ERROR:  function exporter_consommation_tous_compte
() does not exist
LINE 11: SELECT exporter_consommation_tous_compte();
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
lavigny_1175=#



i also paste my file that i pass to psql with "\i myfile"
i'm sorry, it's not very clean, but the point is in "exporter_consommation_compte() and expoerter_consomation_tous_compte().

all remarques are wellcome.

 
 

----------------------------------------
--- INIT DELETING function and type  ---
----------------------------------------

CREATE OR REPLACE FUNCTION reset() RETURNS void AS 
$$
BEGIN
	-- Drop FUNCTION IF EXISTS create_types(); 
	Drop FUNCTION IF EXISTS create_types(); 
	-- Drop IF EXISTS FUNCTION exporter_consommation_compte(ind_compte integer); 
	Drop FUNCTION IF EXISTS exporter_consommation_compte(id_compte integer); 
	Drop FUNCTION IF EXISTS exporter_consommation_liste_compte(); 
	Drop FUNCTION IF EXISTS exporter_consomation_tous_compte(); 
	--Drop IF EXISTS TYPE an_account_communal_export_line; 
	Drop TYPE IF EXISTS export_result; 
	
	-- Drop FUNCTION IF EXISTS eval_card(integer); 
	Drop FUNCTION IF EXISTS eval_cost(integer[]); 
	Drop FUNCTION IF EXISTS eval_units(integer[]); 
END; 
$$
LANGUAGE plpgsql; 


SELECT reset(); 

----------------------------------------
--- TYPES  DECLARATION   -------------
----------------------------------------
CREATE TYPE export_result AS
(
	account_no int, 
	lastname character varying(100), 
	firstname character varying(100), 
	address character varying(100), 
	address_no character varying(100), 
	zip character varying(200), 	
	city character varying(100),
	reference character varying(100), 
	rf_tags_id int[], 
	ecowaste_units integer, 
	masse_en_kg real, 
	cout_absolut real, 
	cout_arrondi real
); 




----------------------------------------
--- FUNCTION DECLARATION   -------------
----------------------------------------
CREATE FUNCTION create_types() RETURNS void AS 
$$
DECLARE 
	nbr integer:= 0; 
BEGIN
	--IF NOT EXISTS TYPE an_account_communal_export_line THEN
	--IF NOT EXISTS an_account_communal_export_line THEN
	-- ELSE 
	-- END IF; 

	IF EXISTS  export_result THEN 
		RAISE NOTICE 'le type - % - existe', export_result; 
	ELSE
		RAISE NOTICE 'le type - % ¯'; 
	END IF; 
END; 
$$ 
LANGUAGE plpgsql; 



----------------------------------------
--- HELPERS  ---------------------------
----------------------------------------
CREATE OR REPLACE FUNCTION eval_units(theCards integer[]) RETURNS integer AS 
$$ 
DECLARE
	_cardUnits integer; 
	_totalUnits integer := 0 ; 
	i integer; 
BEGIN 
	FOREACH i IN array theCards
	LOOP 
		SELECT SUM(weight) from trash_bag WHERE rf_card_id=i INTO _cardUnits; 
		IF _cardUnits is not null THEN
			_totalUnits = _totalUnits + _cardUnits; 
		END IF; 
	END LOOP; 
	return _totalUnits; 
END; 
$$
LANGUAGE plpgsql; 




CREATE OR REPLACE FUNCTION eval_cost(cardsList integer[]) RETURNS REAL AS 
$$ 
DECLARE 
	i integer; 
	_totalCost real := 0.0; 
	_cardCost real := 0.0; 
	--trashes trash_bag%RECORD; 
	trashes trash_bag%ROWTYPE; 

BEGIN 
	-- chercher les déchets des cartes
	-- chercher les prix 
	-- calculer le cout
	FOREACH i IN array cardsList
	LOOP 
		SELECT SUM(price) from trash_bag WHERE rf_card_id=i INTO _cardCost; 
		--raise notice 'on calcule la carte id : % , cout % ', i, _cardCost; 
		IF _cardCost is not null THEN 
			_totalCost = _totalCost + _cardCost; 
		END IF; 
	END LOOP; 
	
	return _totalCost; 
END; 
$$
LANGUAGE plpgsql; 




----------------------------------------
---  MEAT  -----------------------------
----------------------------------------
CREATE FUNCTION exporter_consommation_compte(id_compte integer) RETURNS export_result AS
$$
DECLARE 
	--up_record RECORD; 						 -- ligne de la table user_person
	up_record user_person%ROWTYPE; 

	-- my cards_id as an array
	--theCards integer[]:=ARRAY[1,2,3]; 
	theCards integer[];							 -- tab pour les cartes du compte 
	tempCard integer; 						     -- valeur de recherche 
	-- trashes for cards. 
	-- recover prices per waste 
	result export_result; 
	theCost real; 
BEGIN 
	-- info output
	-- RAISE  NOTICE 'debut du traitement pour le compte no % ', id_compte; 
	-- recupérer les données - user_person - de l'utilisateur du compte 
	SELECT INTO up_record * FROM user_person WHERE user_person_id = id_compte; 
	-- info output
	--RAISE NOTICE 'client: %s %s', up_record.firstname, up_record.lastname;  
	-- result compte user info 		
	result.account_no 	= id_compte; 
	result.firstname	= up_record.firstname; 
	result.lastname		= up_record.lastname; 
	result.address		= up_record.address;  
	result.address_no	= up_record.address_no;  
	result.zip			= up_record.zip; 
	result.city			= up_record.city; 
	result.reference	= up_record.reference; 

	FOR tempCard IN (SELECT rf_card_id FROM rf_card WHERE account_id = id_compte) LOOP
		IF tempCard is not null THEN 
			theCards := array_append(theCards, tempCard) ; 
			-- recupérer les cartes pour le comte 
			-- RAISE NOTICE '   carte id: % ', tempCard; 
		END IF; 
	END LOOP; 

	IF theCards is not null THEN 
		-- on demande le calcul des couts. 
		result.rf_tags_id		= theCards;  
		result.ecowaste_units	= eval_units(theCards); -- todo: le tout d'une fonction, l'éclater ici
		result.cout_absolut		= eval_cost(theCards); 
		result.masse_en_kg 		= result.ecowaste_units * 0.05; 
		result.cout_arrondi 	= 0.00; 
	END IF; 

	return result; 
END ; 
$$ 
LANGUAGE plpgsql; 



--CREATE OR REPLACE FUNCTION exporter_consommation_liste_compte(AnIdAccountlist integer[]) RETURNS setof export_result AS 
-- CREATE OR REPLACE FUNCTION exporter_consommation_liste_compte() RETURNS setof export_result AS 
CREATE OR REPLACE FUNCTION exporter_consommation_liste_compte() RETURNS export_result[] AS 
$$ 
DECLARE
	comptes integer[] := array[10004, 10005, 10006, 10009]; 
	i integer; 
	temp RECORD; 
	result export_result[]; 
BEGIN
	-- for i in 1 .. array_upper(comptes, 1)
	for i in 1 .. array_upper(comptes, 1)
	LOOP 
		-- SELECT * FROM exporter_consommation_compte(comptes[i]) into temp; 
		-- SELECT INTO result * FROM exporter_consommation_compte(comptes[i]) ; 
		-- result :=  exporter_consommation_compte(comptes[i]); 
		result[i] =  exporter_consommation_compte(comptes[i]); 
		-- return next result[i]; 
		-- Copy exporter_consommation_compte(comptes[i])  To 'C:/temp/postgres/test.csv' With CSV;
		-- COPY result[i] TO 'c:/temp/postgres/test.csv' WITH CSV;
	END LOOP; 

	-- on écrit dans un fichier csv pour l'export  
	-- Copy (Select * From result) To 'C:/temp/postgres/test.csv' With CSV;
	-- Copy result[1] To 'C:/temp/postgres/test.csv' With CSV;

	-- Copy result  To 'C:/temp/postgres/test.csv' With CSV;
	-- Copy 'yeahhh! '  To 'C:/temp/postgres/test.csv' With CSV;
	RETURN result; 
END; 
$$
LANGUAGE plpgsql; 


/*
CREATE OR REPLACE FUNCTION exporter_consomation_tous_compte() RETURNS export_result[] AS 
$$
DECLARE 
	row_account user_person%ROWTYPE; 
	result export_result[]; 
	i integer := 0; 
BEGIN 
	FOR row_account IN SELECT * FROM user_person WHERE user_person_id < 1000000 
	LOOP 
		-- RAISE NOTICE '%', exporter_consommation_compte(row_account.user_person_id); 
		result[i] = exporter_consommation_compte(row_account.user_person_id); 
		i := i+1; 
	END LOOP; 
	RETURN result; 
END; 
$$
LANGUAGE plpgsql; 
*/



CREATE OR REPLACE FUNCTION exporter_consomation_tous_compte() RETURNS setof export_result AS 
$$
DECLARE 
	row_account user_person%ROWTYPE; 
	result export_result[]; 
	i integer := 0; 
	aRow export_result; 
BEGIN 

	-- temp table to be returned 
	CREATE TEMPORARY TABLE export_result_table(
		account_no int, 
		lastname character varying(100), 
		firstname character varying(100), 
		address character varying(100), 
		address_no character varying(100), 
		zip character varying(200), 	
		city character varying(100),
		reference character varying(100), 
		rf_tags_id int[], 
		ecowaste_units integer, 
		masse_en_kg real, 
		cout_absolut real, 
		cout_arrondi real
	) on commit drop;
		


	FOR row_account IN SELECT * FROM user_person WHERE user_person_id < 1000000 
	LOOP 
		-- RAISE NOTICE '%', exporter_consommation_compte(row_account.user_person_id); 
		-- result[i] = exporter_consommation_compte(row_account.user_person_id); 
		aRow = exporter_consommation_compte(row_account.user_person_id); 
		-- INSERT INTO  temp_payroll_val VALUES(
		 INSERT INTO  export_result_table VALUES(
				aRow.account_no, 
				aRow.lastname, 
				aRow.firstname, 
				aRow.address , 
				aRow.address_no, 
				aRow.zip, 	
				aRow.city,
				aRow.reference, 
				aRow.rf_tags_id , 
				aRow.ecowaste_units, 
				aRow.masse_en_kg , 
				aRow.cout_absolut, 
				aRow.cout_arrondi
		); 
			
		-- i := i+1; 
		-- RETURN NEXT export_result_table ;
	END LOOP; 
	-- RETURN result; 
	-- RETURN temp_payroll_val; 
	RETURN export_result_table; 
END; 
$$
LANGUAGE plpgsql; 


--- adding a row of type export_result 
--- adding the tmeporary table 
--- "on commit drop" at the end of the table
--- 


/*
	utilisation de la procedure 
*/
--SELECT create_types(); 

--SELECT * FROM exporter_consommation_compte(10015); 
-- NOTE: test court 
--SELECT * FROM exporter_consommation_liste_compte(); 
-- SELECT exporter_consommation_liste_compte(); 
--
SELECT exporter_consommation_tous_compte(); 
--SELECT * FROM exporter_consommation_tous_compte(); 
--
-- NOTE: test long final 
--SELECT * FROM exporter_consomation_tous_compte(); 
--
--
--
--

Select allOpen in new window

sedgwick, i'm veeeery intersted in succeed with this solution, it's the most elegant for my point of view.

thank you in advance for reach the end ;)

try return select * from temp table:

so instead of:

RETURN NEXT export_result_table ;

Select allOpen in new window

try:

RETURN QUERY(
select * from export_result_table
);

Select allOpen in new window

thank you so much !!!
i succeed, i spend some time reading the link you send me and in fact i had to loop again on the temporary table with a return next ;)

i'm happy ;)

thank you again,
happy Christamas !!

happy Christmas mate, take care :)

yeah mate!  And a Happy New YEAR !!!