[SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Guys!
I need to make a complex query. I am thinking to use plpgsql BUT I am
confused how I can solve this.
What I have:
CREATE TABLE test
(
code varchar(15),
description varchar(60),
group varchar(10),
quant float8,
price float8,
total float8
)
WITHOUT OIDS;
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 5, 0.90, 9);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 20, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 10, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.8, 80);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 200, 0.8, 160);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.9, 90);
I need an subtotal for all the products with the same group and that the
query be ordered by the bigger subtotal.
For example, I need an output like this:
Ex.
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99120 | PRODUCT C |10 | 0.8 | 8 | 8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160| 168
99120 | PRODUCT C | 100 | 0.9 |90 | 667
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
The subtotal column must sum all the products with the same code and put
the result in order of the bigger sultotals.
Only make a function that sum the last value + the subtotal it's not
hard BUT how I can make the subtotal restart when the code changes and
how I will order the result by the bigger subtotal code groups?
Thanks!
Rodrigo Carvalhaes
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] PostgreSQL and Delphi 6
Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi !
This is not possible because the query will have a time interval and the
subtotal will change due the intervals passed to the query...
To get the subtotal I already know how to do it (see below) but the
problem is get the correct output, ordering by the bigger totals
agrouped by product code
CREATE TYPE subtotal_type AS
(code varchar(15),
description varchar(60),
quant float8,
price float8,
total float8,
subtotal float8);
CREATE OR REPLACE FUNCTION product_sales()
RETURNS SETOF subtotal_type AS
$BODY$
DECLARE
tbrow RECORD;
sbrow subtotal_type;
BEGIN
sbrow.subtotal := 0;
FOR tbrow IN
SELECT code, description, quant, price, total FROM test ORDER BY code
LOOP
IF sbrow.code = tbrow.code THEN
sbrow.subtotal := sbrow.subtotal + tbrow.total;
ELSE
sbrow.subtotal := tbrow.total;
END IF;
sbrow.code := tbrow.code;
sbrow.description := tbrow.description;
sbrow.quant := tbrow.quant;
sbrow.price := tbrow.price;
sbrow.total := tbrow.total;
RETURN NEXT sbrow;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
With this function my output is:
dadosadv=# SELECT * FROM product_sales();
code | description | quant | price | total | subtotal
---+-+---+---+---+--
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
(12 rows)
The only problem that I have is that I need to order by the
max(subtotal) aggrouped by code. My desired output is:
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
Any tip?
Regards,
Rodrigo Carvalhaes
Gnanavel Shanmugam wrote:
I think it will be better to add one more column for subtotal and
write an "on before insert" trigger to update the subtotal with sum of
total.
-Original Message-
From: [EMAIL PROTECTED]
Sent: Thu, 16 Jun 2005 00:56:42 -0300
To: pgsql-sql@postgresql.org
Subject: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Guys!
I need to make a complex query. I am thinking to use plpgsql BUT I am
confused how I can solve this.
What I have:
CREATE TABLE test
(
code varchar(15),
description varchar(60),
group varchar(10),
quant float8,
price float8,
total float8
)
WITHOUT OIDS;
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 5, 0.90, 9);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 20, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 10, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.8, 80);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 200, 0.8, 160);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.9, 90);
I need an subtotal for all the products with the same group and that the
query be ordered by the bigger subtotal.
For example, I need an output like this:
Ex.
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99120 |
Re: [SQL] PostgreSQL and Delphi 6
Hi Adrian, You're right. What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). If you find a better solution, please inform me. Regards, Rodrigo Din Adrian wrote: We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] PostgreSQL and Delphi 6
Hi Adrian! Yes, the user have to wait until the refresh finishes... That's a problem but we didn't found any other solution... Regards, Rodrigo Carvalhaes Din Adrian wrote: I am sorry - I don't understand (or my english is bad or I don't know what you mean). What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). So: When the user push the 'post' button the driver automatically refresh the current dataset and the user have to wait many seconds (or min?!) until it's finish. ok? How did you say you avoided this ? Adrian Din, Om Computer & SoftWare Bucuresti, Romania On Thu, 16 Jun 2005 10:29:38 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi Adrian, You're right. What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). If you find a better solution, please inform me. Regards, Rodrigo Din Adrian wrote: We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Gnanavel,
Thanks for your promptly answer. Yes, your solution solves this problem
BUT the point is that I don't wanna a solution that works only if the
codes are in desc order.
For example, if the codes are on the order above:
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 10, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.4, 80);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 200, 0.80, 160);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 5, 0.9, 9);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 20, 0.80, 8);
With this data your query result is wrong:
dadosadv=# SELECT * FROM (SELECT * FROM product_sales() ) t order by
t.code desc, t.subtotal;
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99130 | PRODUCT b |10 | 1.1 |11 | 11
99130 | PRODUCT b |10 | 1.1 |11 | 22
99130 | PRODUCT b |10 | 1.1 |11 | 33
99130 | PRODUCT b |20 | 0.8 | 8 | 41
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
99100 | PRODUCT A |10 | 1 |10 | 10
99100 | PRODUCT A | 5 | 0.9 | 9 | 19
99100 | PRODUCT A | 100 | 0.9 |90 | 109
99100 | PRODUCT A |10 | 1.1 |11 | 120
The point is that I wanna that the output always be ordered by the
bigger subtotal groups, indepent of the order of the codes...
Do you have any idea how I can do it?
Thanks,
Rodrigo Carvalhaes
Gnanavel Shanmugam wrote:
This might work,
select * from (SELECT * FROM product_sales()) t order by t.code
desc,t.subtotal;
with regards,
S.Gnanavel
-Original Message-
From: [EMAIL PROTECTED]
Sent: Thu, 16 Jun 2005 10:07:15 -0300
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Subject: Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi !
This is not possible because the query will have a time interval and the
subtotal will change due the intervals passed to the query...
To get the subtotal I already know how to do it (see below) but the
problem is get the correct output, ordering by the bigger totals
agrouped by product code
CREATE TYPE subtotal_type AS
(code varchar(15),
description varchar(60),
quant float8,
price float8,
total float8,
subtotal float8);
CREATE OR REPLACE FUNCTION product_sales()
RETURNS SETOF subtotal_type AS
$BODY$
DECLARE
tbrow RECORD;
sbrow subtotal_type;
BEGIN
sbrow.subtotal := 0;
FOR tbrow IN
SELECT code, description, quant, price, total FROM test ORDER BY code
LOOP
IF sbrow.code = tbrow.code THEN
sbrow.subtotal := sbrow.subtotal + tbrow.total;
ELSE
sbrow.subtotal := tbrow.total;
END IF;
sbrow.code := tbrow.code;
sbrow.description := tbrow.description;
sbrow.quant := tbrow.quant;
sbrow.price := tbrow.price;
sbrow.total := tbrow.total;
RETURN NEXT sbrow;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
With this function my output is:
dadosadv=# SELECT * FROM product_sales();
code | description | quant | price | total | subtotal
---+-+---+---+---+--
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
(12 rows)
The only problem that I have is that I need to order by the
max(subtotal) aggrouped by code. My desired output is:
code
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Greg, Thanks for your reply. Yes, same group of code... Perfect solution, simple and efficient. Thank you very much!!! Cheers, Rodrigo Carvalhaes Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I need an subtotal for all the products with the same group and that the query be ordered by the bigger subtotal. (please proofread: the subtotals in your example output did not add up) By "same group" I presume you mean the same code, as you don't actually use the "group varchar(10)" column you created in your example. A major problem you have is that you have no other way of ordering the rows except by the code. So having a running subtotal is fairly pointless, as the items within each code will appear randomly. Since only the grand total for each code is significant, you could write something like this: SELECT t.*, s.subtotal FROM (SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1) s, test t WHERE s.code = t.code ORDER BY subtotal desc; code | description | quant | price | total | subtotal - ---+-+---+---+---+-- 99120 | PRODUCT C |10 | 0.8 | 8 | 338 99120 | PRODUCT C | 100 | 0.8 |80 | 338 99120 | PRODUCT C | 200 | 0.8 | 160 | 338 99120 | PRODUCT C | 100 | 0.9 |90 | 338 92110 | PRODUCT A |10 | 1 |10 | 120 92110 | PRODUCT A | 5 | 0.9 | 9 | 120 92110 | PRODUCT A | 100 | 0.9 |90 | 120 92110 | PRODUCT A |10 | 1.1 |11 | 120 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |20 | 0.8 | 8 | 41 If you don't need all that intermediate stuff: SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1 ORDER BY 2 DESC; code | subtotal - ---+-- 99120 | 338 92110 | 120 92190 | 41 If you do need the other rows, you will have to specify a way of ordering the rows within a code group. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506161458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM 79gJZ2hUgDk1jL3LDQv3le0= =mpnW -END PGP SIGNATURE- -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Array in plpgsql with composite type
Hi Guys! I need to make an array with composite type (varchar, float8) to get data from a table (description and price) BUT I am having no success... I searched a lot on the internet without success. On the PostgreSQL manual there is no information how to handle array with plpgsql with composite types. I tried to create a type (varchar, float8) but without success... Can anyone send me some examples or references for arrays in plpgsql (I am using PostgreSQL 8.0.3). Regards, Rodrigo Carvalhaes -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] RETURN SET OF DATA WITH CURSOR
Hi !
I am making one plpgsql function and I need to return of setof data
using a cursor.
The problem is that my function is returning only the first row and
column of the query. This query have more than 80 columns and 1.000
rows. Enyone have any tip to give me?
Here the fuction...
CREATE OR REPLACE FUNCTION rel_faturamento("varchar","varchar")
RETURNS refcursor AS
$BODY$
DECLARE
data_inicial ALIAS FOR $1;
data_final ALIAS FOR $2;
ref refcursor;
fat_cursor CURSOR FOR
SELECT * FROM SF2010 SF2
INNER JOIN SD2010 SD2 ON (SD2.D2_DOC = SF2.F2_DOC)
INNER JOIN SB1010 SB1 ON (SB1.B1_COD = SD2.D2_COD)
INNER JOIN SA1010 SA1 ON (SF2.F2_CLIENTE = SA1.A1_COD)
WHERE F2_EMISSAO >= data_inicial AND F2_EMISSAO <=
data_final
AND SF2.D_E_L_E_T_<> '*' AND sd2.d2_tes IN (SELECT f4_codigo FROM
sf4010 WHERE d_e_l_e_t_ <> '*' AND f4_duplic = 'S' AND f4_codigo >= '500')
ORDER BY SF2.F2_EMISSAO, SF2.F2_DOC, SF2.F2_HORA;
BEGIN
OPEN fat_cursor;
LOOP
FETCH fat_cursor INTO ref;
RETURN ref;
END LOOP;
CLOSE fat_cursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
When I execute it, I only receive the return below:
SELECT rel_faturamento('20051201','20051231');
rel_faturamento
-
010876
(1 row)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] RETURN SET OF DATA WITH CURSOR
Hi Richard, Thanks for your promptly answer. I don't have experience returning refcursor but my choice would be it. I read the documentation but I didn't find any example with my necessity... Could you give me a small example on the same "basis" that I want? Thanks, Rodrigo Carvalhaes Richard Huxton wrote: grupos wrote: Hi ! I am making one plpgsql function and I need to return of setof data using a cursor. The problem is that my function is returning only the first row and column of the query. This query have more than 80 columns and 1.000 rows. Enyone have any tip to give me? Yes - decide whether you are returning a set of rows or a refcursor. Check the plpgsql docs again for how to return SETOF using the RETURN NEXT statement. Then you call your function like: SELECT * FROM my_func(); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
