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

hello experts,

i'm using postgresql function to get data to a .NET application.
so far i succeed to accumulate my datas in a type created in psql. Now, i recover values with npgsql, i pass a executeScalar as i have all function response in only one line.
when i recover datas with npgsql i have all them in the first cell of the datatable.
here's what my postgresql looks like, this response has three rows.

question:

what would be an elegant way to recover my values as i need to output them to csv file.
 should i parse the string and create my lines ? if so what lib would help, is there something like XML parser but for postgresql responses ?
 
or is there a way to get the answer of postgres query in a better recovery solution with npgsql ?

thank you in advance for help.


----------------- answers i receive from pgsql function in a string .-------------------------------
"{\"(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)\"}"


toshi
Erwin PombettAsked:
Who is Participating?
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
change your main function to return setof:
CREATE OR REPLACE FUNCTION exporter_consomation_tous_compte()
RETURNS setof export_result

which means it is going to return a rowset of export_resultrows.

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; 
row export_result;
BEGIN 

--create 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); 
row = exporter_consommation_compte(row_account.user_person_id); 
INSERT INTO temp_payroll_val VALUES(row.account_no,
row.lastname , 
	row.firstname , 
	row.address , 
	row.address_no , 
	row.zip , 	
	row.city ,
	row.reference, 
	row.rf_tags_id , 
	row.ecowaste_units, 
	row.masse_en_kg , 
	row.cout_absolut , 
	row.cout_arrondi, 
)
		--i := i+1; 
	END LOOP; 
	--here you return the temp table...
END; 
$$
LANGUAGE plpgsql; 

Open in new window


i'm not in front of postresql environemetn but i'm sure you can fill up the missing stuff.

check here for example:
 Temporary table inside a PostgreSQL function
0
 
Meir RivkinFull stack Software EngineerCommented:
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
0
 
Erwin PombettAuthor Commented:
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Meir RivkinFull stack Software EngineerCommented:
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);

Open in new window

0
 
Erwin PombettAuthor Commented:
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 ?
0
 
Erwin PombettAuthor Commented:
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
0
 
Erwin PombettAuthor Commented:
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_
0
 
Meir RivkinFull stack Software EngineerCommented:
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

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
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.

scerenshot of result
0
 
Erwin PombettAuthor Commented:
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(); 
--
--
--
--

Open 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 ;)
0
 
Meir RivkinFull stack Software EngineerCommented:
try return select * from temp table:

so instead of:

RETURN NEXT export_result_table ;

Open in new window

try:
RETURN QUERY(
select * from export_result_table
);

Open in new window

0
 
Erwin PombettAuthor Commented:
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 !!
0
 
Meir RivkinFull stack Software EngineerCommented:
happy Christmas mate, take care :)
0
 
Erwin PombettAuthor Commented:
yeah mate!  And a Happy New YEAR !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.