Обсуждение: Cursor Example Needed
When I execute the SELECT statement directly I get: psql:table.sql:28: out of memory for query result I've read the way around this is to use cursors. So I read and I see that I can use a FOR statement but I need that insidea function. So far, I've come up with this: > CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$ > > DECLARE > xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06'; > > BEGIN > FOR my_rec IN xyz LOOP > -- xyxyxy what to do where? xyxyxy These things don't work: > COPY ( my_rec ) TO stdout; > SELECT * FROM my_rec; > etc. > END LOOP; > > RETURN 5; > END > $$ LANGUAGE plpgsql; > > SELECT * FROM blah(); I just want to output the rows being selected as text. Basically I want the same output that the SELECT statement wouldproduce if it didn't fail. Also, what should I do with the SELECT * FROM blah() statement? I'm doing that just to get blah() to execute. I have afeeling I'm way far off base. Thank you, Perry
Вложения
On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote: > When I execute the SELECT statement directly I get: > > psql:table.sql:28: out of memory for query result psql will do this automatically if you tell it to: http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html cursors can work with your code, but they need participation from the client side. basically you stage the cursor then repeatedly FETCH until done. merlin
On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote: >> When I execute the SELECT statement directly I get: >> >> psql:table.sql:28: out of memory for query result > > psql will do this automatically if you tell it to: > http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html > > cursors can work with your code, but they need participation from the > client side. basically you stage the cursor then repeatedly FETCH > until done. Ok. I'll try that. I'm still curious how to do it using cursors if anyone wants to provide a sample. Thank you, Perry
Вложения
On 10/28/2013 02:27 PM, Perry Smith wrote: > > On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > >> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote: >>> When I execute the SELECT statement directly I get: >>> >>> psql:table.sql:28: out of memory for query result >> >> psql will do this automatically if you tell it to: >> http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html >> >> cursors can work with your code, but they need participation from the >> client side. basically you stage the cursor then repeatedly FETCH >> until done. > > Ok. I'll try that. I'm still curious how to do it using cursors if anyone wants > to provide a sample. http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html > > Thank you, > Perry > -- Adrian Klaver adrian.klaver@gmail.com
On Oct 28, 2013, at 5:21 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 10/28/2013 02:27 PM, Perry Smith wrote: >> >> On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >> >>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote: >>>> When I execute the SELECT statement directly I get: >>>> >>>> psql:table.sql:28: out of memory for query result >>> >>> psql will do this automatically if you tell it to: >>> http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html >>> >>> cursors can work with your code, but they need participation from the >>> client side. basically you stage the cursor then repeatedly FETCH >>> until done. >> >> Ok. I'll try that. I'm still curious how to do it using cursors if anyone wants >> to provide a sample. > > http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html Yea, I looked at those. Part of my original email got lost. Here is from the original email: > CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$ > > DECLARE > xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06'; > > BEGIN > FOR my_rec IN xyz LOOP > -- xyxyxy what to do where? xyxyxy These things don't work: > COPY ( my_rec ) TO stdout; > SELECT * FROM my_rec; > etc. > END LOOP; > > RETURN 5; > END > $$ LANGUAGE plpgsql; > > SELECT * FROM blah(); Perhaps my question is what do I do with my_rec once I have it? I want output the same as if I did the top SELECT statement directly.
Вложения
On 10/28/2013 03:49 PM, Perry Smith wrote: > > On Oct 28, 2013, at 5:21 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> On 10/28/2013 02:27 PM, Perry Smith wrote: >>> >>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >>> >>>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote: >>>>> When I execute the SELECT statement directly I get: >>>>> >>>>> psql:table.sql:28: out of memory for query result >>>> >>>> psql will do this automatically if you tell it to: >>>> http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html >>>> >>>> cursors can work with your code, but they need participation from the >>>> client side. basically you stage the cursor then repeatedly FETCH >>>> until done. >>> >>> Ok. I'll try that. I'm still curious how to do it using cursors if anyone wants >>> to provide a sample. >> >> http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html > > Yea, I looked at those. Part of my original email got lost. Here is from the original > email: Well the point of the example at the above link is that you do not have to do it in a function:) > >> CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$ >> >> DECLARE >> xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06'; >> >> BEGIN >> FOR my_rec IN xyz LOOP >> -- xyxyxy what to do where? xyxyxy These things don't work: >> COPY ( my_rec ) TO stdout; >> SELECT * FROM my_rec; >> etc. >> END LOOP; >> >> RETURN 5; >> END >> $$ LANGUAGE plpgsql; >> >> SELECT * FROM blah(); > > Perhaps my question is what do I do with my_rec once I have it? I want > output the same as if I did the top SELECT statement directly. The docs do a good job of illustrating: http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html > -- Adrian Klaver adrian.klaver@gmail.com
On Mon, Oct 28, 2013 at 5:49 PM, Perry Smith <pedzsan@gmail.com> wrote: > On Oct 28, 2013, at 5:21 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> On 10/28/2013 02:27 PM, Perry Smith wrote: >>> >>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >>> >>>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith <pedzsan@gmail.com> wrote: >>>>> When I execute the SELECT statement directly I get: >>>>> >>>>> psql:table.sql:28: out of memory for query result >>>> >>>> psql will do this automatically if you tell it to: >>>> http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html >>>> >>>> cursors can work with your code, but they need participation from the >>>> client side. basically you stage the cursor then repeatedly FETCH >>>> until done. >>> >>> Ok. I'll try that. I'm still curious how to do it using cursors if anyone wants >>> to provide a sample. >> >> http://www.postgresql.org/docs/9.3/interactive/sql-fetch.html > > Yea, I looked at those. Part of my original email got lost. Here is from the original > email: > >> CREATE OR REPLACE FUNCTION blah() RETURNS integer AS $$ >> >> DECLARE >> xyz CURSOR FOR SELECT * FROM shipped_files WHERE aix_file = '/usr/lib/drivers/vioentdd' AND service_pack = '7100-01-06'; >> >> BEGIN >> FOR my_rec IN xyz LOOP >> -- xyxyxy what to do where? xyxyxy These things don't work: >> COPY ( my_rec ) TO stdout; >> SELECT * FROM my_rec; >> etc. >> END LOOP; >> >> RETURN 5; >> END >> $$ LANGUAGE plpgsql; >> >> SELECT * FROM blah(); > > Perhaps my question is what do I do with my_rec once I have it? I want > output the same as if I did the top SELECT statement directly. pl/pgsql is a language for creating functions. From within a function, you can interact with the database directly or with the calling query through returning data. You can't really send data to the client side (unless you count 'COPY' or RAISE NOTICE'). So although pl/pgsql FETCH has similar mechanics to SQL fetch, they are very different in that pl/pgsql FETCH is generally directed at variables. pl/pgsql FETCH is somewhat baroque; typically it's cleaner and easier to just iterate with a vanilla FOR-IN loop. The only reasons why I use cursors inside functions any more that I can think of off the top of my head are when you need fancy scrolling (say to cycle through a result set multiple times) or to pass data around between different functions (but that is mostly displaced by temp tables for large sets and or record arrays for very small ones) merlin
On 10/28/2013 3:58 PM, Adrian Klaver wrote: > The docs do a good job of illustrating: > > http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html thats for cursors created within a plpgsql function. I think what the OP wants is a top level cursor, which is a different thing... see http://www.postgresql.org/docs/current/static/sql-declare.html http://www.postgresql.org/docs/current/static/sql-fetch.html http://www.postgresql.org/docs/current/static/sql-close.html the fetch page shows an example of the complete usage in the context of a database transaction. -- john r pierce 37N 122W somewhere on the middle of the left coast
On Oct 28, 2013, at 6:13 PM, John R Pierce <pierce@hogranch.com> wrote: > On 10/28/2013 3:58 PM, Adrian Klaver wrote: >> The docs do a good job of illustrating: >> >> http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html > > thats for cursors created within a plpgsql function. > > I think what the OP wants is a top level cursor, which is a different thing... > > see > http://www.postgresql.org/docs/current/static/sql-declare.html > http://www.postgresql.org/docs/current/static/sql-fetch.html > http://www.postgresql.org/docs/current/static/sql-close.html > > the fetch page shows an example of the complete usage in the context of a database transaction. Thank you to Merlin. I now understand better where my confusion was. John: Those examples are great except there is no way that I know of to loop on the "top level" as you call it. I'm trying to do something that I can give to psql which will loop through the entire set that is produced. I came across the FOR-IN loop but that needs a function. But as Merlin points out, the function is on the server side and I need the loop on the client side. Perhaps the \set FETCH_COUNT 500000 solution that Merln point out originally is the only choice. I feel like I've learned a lot even though it might not have been what I was originally trying to learn :-) Thank you again, Perry
Вложения
On 10/28/2013 04:36 PM, Perry Smith wrote: > > On Oct 28, 2013, at 6:13 PM, John R Pierce <pierce@hogranch.com> wrote: > >> On 10/28/2013 3:58 PM, Adrian Klaver wrote: >>> The docs do a good job of illustrating: >>> >>> http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html >> >> thats for cursors created within a plpgsql function. >> >> I think what the OP wants is a top level cursor, which is a different thing... >> >> see >> http://www.postgresql.org/docs/current/static/sql-declare.html >> http://www.postgresql.org/docs/current/static/sql-fetch.html >> http://www.postgresql.org/docs/current/static/sql-close.html >> >> the fetch page shows an example of the complete usage in the context of a database transaction. > > Thank you to Merlin. I now understand better where my confusion was. > > John: > > Those examples are great except there is no way that I know of to loop on > the "top level" as you call it. I'm trying to do something that I can give to > psql which will loop through the entire set that is produced. The FETCH example shows you how. You do not have FOR but you do have FORWARD and if you DECLARE SCROLL, BACKWARD. If you need to do actions on each row as it is fetched then you will probably need to do it in a function. Your original post though was concerned with dealing with an out of memory error caused by returning to large a result set at one time and that can be handled in psql as illustrated. > > I came across the FOR-IN loop but that needs a function. But as Merlin > points out, the function is on the server side and I need the loop on the > client side. > > Perhaps the > > \set FETCH_COUNT 500000 > > solution that Merln point out originally is the only choice. > > I feel like I've learned a lot even though it might not have been what > I was originally trying to learn :-) > > Thank you again, > Perry > -- Adrian Klaver adrian.klaver@gmail.com
On Oct 28, 2013, at 6:50 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 10/28/2013 04:36 PM, Perry Smith wrote: >> >> On Oct 28, 2013, at 6:13 PM, John R Pierce <pierce@hogranch.com> wrote: >> >>> On 10/28/2013 3:58 PM, Adrian Klaver wrote: >>>> The docs do a good job of illustrating: >>>> >>>> http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html >>> >>> thats for cursors created within a plpgsql function. >>> >>> I think what the OP wants is a top level cursor, which is a different thing... >>> >>> see >>> http://www.postgresql.org/docs/current/static/sql-declare.html >>> http://www.postgresql.org/docs/current/static/sql-fetch.html >>> http://www.postgresql.org/docs/current/static/sql-close.html >>> >>> the fetch page shows an example of the complete usage in the context of a database transaction. >> >> Thank you to Merlin. I now understand better where my confusion was. >> >> John: >> >> Those examples are great except there is no way that I know of to loop on >> the "top level" as you call it. I'm trying to do something that I can give to >> psql which will loop through the entire set that is produced. > > The FETCH example shows you how. You do not have FOR but you do have FORWARD and if you DECLARE SCROLL, BACKWARD. If youneed to do actions on each row as it is fetched then you will probably need to do it in a function. Your original postthough was concerned with dealing with an out of memory error caused by returning to large a result set at one time andthat can be handled in psql as illustrated. Yes. I finally understand your (or someone's) original reply. Thank you to all who helped me out. Perry