Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.
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,\\\"
toshi
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,\\\"
toshi
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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:
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);
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'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
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
change your main function to return setof:
CREATE OR REPLACE FUNCTION exporter_consomation_tous_
RETURNS setof export_result
which means it is going to return a rowset of export_resultrows.
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
CREATE OR REPLACE FUNCTION exporter_consomation_tous_
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;
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
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/ep
returning set
LINE 78: RETURN export_result_table;
^
HINT: Use RETURN NEXT or RETURN QUERY.
psql:c:/coderie/plpgsql/ep
() does not exist
LINE 12: SELECT * FROM exporter_consommation_tous
^
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_
$$
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_comp
-- result\[i\] = exporter_consommation_comp
aRow = exporter_consommation_comp
-- 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
toshi_
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/ep
returning set
LINE 78: RETURN export_result_table;
^
HINT: Use RETURN NEXT or RETURN QUERY.
psql:c:/coderie/plpgsql/ep
() does not exist
LINE 12: SELECT * FROM exporter_consommation_tous
^
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_
$$
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_comp
-- result\[i\] = exporter_consommation_comp
aRow = exporter_consommation_comp
-- 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
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("r
to parse the string result simply call it like this:
Dim result = ExportResultData.Create("r
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
the file that i'm reading contains the string result from your question:
you should pass the result from your DB function.
"{\"(10004,Aeby,Aloys,\\\"Rte du Vignoble\\\",33,1175,lala, \\\"\\\",\ \\"{1,2}\\ \",968,48. 4000015,26 .6200008,0 )\",\"(100 05,Aeby,\\ \"Andr¿ Christian\\\",\\\"Route de St-Livres\\\",7,1175,lala, AEBYAN000, \\\"{3,4}\ \\",1466,7 3.3000031, 40.3150024 ,0)\",\"(1 0006,Aeby, Freddy,\\\ "Route du Moulin Martinet\\\",11,1175,lala, AEBYFR000, ,,,,)\",\" (10009,All emand,\\\" Pierre Ambroise\\\",\\\"Rte du Vignoble\\\",3,1175,Lavign y,ALLEPI00 0,\\\"{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
---------------------- ERROR --------------------------
psql:c:/coderie/plpgsql/ep
returning set
LINE 78: RETURN export_result_table;
^
HINT: Use RETURN NEXT or RETURN QUERY.
psql:c:/coderie/plpgsql/ep
() does not exist
LINE 11: SELECT exporter_consommation_tous
^
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_com
all remarques are wellcome.
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 ;)
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
---------------------- ERROR --------------------------
psql:c:/coderie/plpgsql/ep
returning set
LINE 78: RETURN export_result_table;
^
HINT: Use RETURN NEXT or RETURN QUERY.
psql:c:/coderie/plpgsql/ep
() does not exist
LINE 11: SELECT exporter_consommation_tous
^
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_com
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();
--
--
--
--
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:
so instead of:
RETURN NEXT export_result_table ;
RETURN QUERY(
select * from export_result_table
);
Premium Content
You need an Expert Office subscription to comment.Start Free Trial