Peter Koukoulis writes:
> This is my first cursor attempt:
> according to docs
> DECLARE
> curs1 refcursor;
> curs2 CURSOR FOR SELECT * FROM tenk1;
> curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
> this should work, but getting error:
> ft_node=# declare c
Hi
This is my first cursor attempt:
according to docs
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
this should work, but getting error:
ft_node=# declare cur_test1 CURSOR (key integer) for sel
rameshparnanditech wrote
> Hello,
>in postgres function (id bigint ),the following code not return
> any value with artNums ,But when i do select statement ony it's output
> the
> values with out include cursor
>
> i.e,cursor problem ..?
>
> please let me know what should i do to ge
Hello,
in postgres function (id bigint ),the following code not return
any value with artNums ,But when i do select statement ony it's output the
values with out include cursor
i.e,cursor problem ..?
please let me know what should i do to getvalues from cursor, but i dont
where i mis
On Oct 28, 2013, at 6:50 PM, Adrian Klaver wrote:
> On 10/28/2013 04:36 PM, Perry Smith wrote:
>>
>> On Oct 28, 2013, at 6:13 PM, John R Pierce 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/
On 10/28/2013 04:36 PM, Perry Smith wrote:
On Oct 28, 2013, at 6:13 PM, John R Pierce 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 f
On Oct 28, 2013, at 6:13 PM, John R Pierce 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
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
ht
On Mon, Oct 28, 2013 at 5:49 PM, Perry Smith wrote:
> On Oct 28, 2013, at 5:21 PM, Adrian Klaver wrote:
>
>> On 10/28/2013 02:27 PM, Perry Smith wrote:
>>>
>>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote:
>>>
On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote:
> When I execute th
On 10/28/2013 03:49 PM, Perry Smith wrote:
On Oct 28, 2013, at 5:21 PM, Adrian Klaver wrote:
On 10/28/2013 02:27 PM, Perry Smith wrote:
On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote:
On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote:
When I execute the SELECT statement directly I
On Oct 28, 2013, at 5:21 PM, Adrian Klaver wrote:
> On 10/28/2013 02:27 PM, Perry Smith wrote:
>>
>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote:
>>
>>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote:
When I execute the SELECT statement directly I get:
psql:table.sq
On 10/28/2013 02:27 PM, Perry Smith wrote:
On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote:
On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith 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 t
On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote:
> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith 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.blo
On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith 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 c
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 inside a function.
So far, I've come up with this:
> CREATE OR REPLACE FUNC
On Friday, December 28, 2012 10:58 AM Harry wrote:
> Hi,
> Kindly see the below attached statements related to Cursor Fetch Issue it's
> still residing as a process.
>500 20222 31036 79 Dec27 ?16:22:31 postgres: user1 sampledb
>192.168.0.40[36022] FETCH
>500 20829 31036 81 Dec2
Hi,
Kindly see the below attached statements related to Cursor Fetch Issue it's
still residing as a process.
500 20222 31036 79 Dec27 ?16:22:31 postgres: user1 sampledb
192.168.0.40[36022] FETCH
500 20829 31036 81 Dec27 ?16:18:48 postgres: user1 sampledb
192.168.0.40[5759
No any statements as u mentioned.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5738120.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
On Thursday, December 27, 2012 2:44 PM Harry wrote:
> Below is the Linux ps -ef | grep postgres output :-
>
> 501 12163 5473 0 Dec19 ?00:00:00 postgres: enterprisedb
> sampledb 192.168.0.231[53991] ?EDB-SPL Procedure successfully completed
> 501 12167 5473 0 Dec19 ?00
Below is the Linux ps -ef | grep postgres output :-
501 12163 5473 0 Dec19 ?00:00:00 postgres: enterprisedb
sampledb 192.168.0.231[53991] ?EDB-SPL Procedure successfully completed
501 12167 5473 0 Dec19 ?00:00:00 postgres: enterprisedb
sampledb 192.168.0.231[53995] ?E
On Thursday, December 27, 2012 11:51 AM Harry wrote:
> Hi Amit,
> Thanks for Reply.
> Kindly see my below output.
>
> Also, tried to Kill it Firstly by using Cancel Backend and then
> Terminate
> Backend output showing "True" but still remaining as a process (i.e. in
> pg_stat_activity).
Can you
On Thursday, December 27, 2012 11:51 AM Harry wrote:
> Hi Amit,
> Thanks for Reply.
> Kindly see my below output.
> 16650;"sampledb";11965;10;"enterprisedb";"";"192.168.0.231";"";53897;"*
> 2012-12-19
> 11:39:48.234799+05:30";"2012-12-19 11:39:53.288441+05:30";"2012-12-19
> 11:39:53.288441+05:30*";
Hi Amit,
Thanks for Reply.
Kindly see my below output.
16650;"sampledb";11965;10;"enterprisedb";"";"192.168.0.231";"";53897;"*2012-12-19
11:39:48.234799+05:30";"2012-12-19 11:39:53.288441+05:30";"2012-12-19
11:39:53.288441+05:30*";f;"DECLARE
BEGIN
EXEC
16650;"sampledb";12156;10;"enterprisedb";"";"
On Wednesday, December 26, 2012 5:12 PM Harry wrote:
> Hi All,
>
> I am getting a problem i.e. in database while checking pg_stat_activity
> faced issue statement is residing as
> process
> for couple of days also,
How have you concluded, it stays for couple of days?
pg_stat_activity will show
Hi All,
I am getting a problem i.e. in database while checking pg_stat_activity
faced issue statement is residing as process
for couple of days also, not able to kill them through pg_terminate_backend
function.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Cursor-
On Dec 21, 2011, at 0:36, Dinesh Kumara wrote:
> Greetings..
>
> Please advice me how to stop current iteration of cursor loop and continue
> with next iteration .
>
> ...
> contractlistquery = '';
> OPEN rec_contractlist FOR EXECUTE contractlistquery;
> LOOP
> FETCH rec_contractli
Greetings..
Please advice me how to stop current iteration of cursor loop and
continue with next iteration .
...
contractlistquery = '';
OPEN rec_contractlist FOR EXECUTE contractlistquery;
LOOP
FETCH rec_contractlist INTO
contract_from_date,contract_to_date,contract_inv_type,con
"Andy Chambers" writes:
> Is there anywhere in the postgres catalog where one can access metadata
> about a held cursor.
The pg_cursors system view offers some info ...
> Type information
> in particular would be really useful.
... but not that. Usually the best way to get information about
Hi All,
Is there anywhere in the postgres catalog where one can access metadata
about a held cursor. Type information
in particular would be really useful.
Cheers,
Andy
--
Andy Chambers
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscripti
Thank you very much
Hello
2011/3/25 Luca Santaniello:
Hi all,
I need use dinamic string for my cursor... My code is:
query varchar := 'field1, field2 from ''' || tableName ||''' ; //has
dinamic params
use a FOR EXECUTE statement
FOR r IN EXECUTE 'SELECT .. FROM ' || quote_ident(ta
Hello
2011/3/25 Luca Santaniello :
> Hi all,
>
> I need use dinamic string for my cursor... My code is:
>
> query varchar := 'field1, field2 from ''' || tableName ||''' ; //has
> dinamic params
>
use a FOR EXECUTE statement
FOR r IN EXECUTE 'SELECT .. FROM ' || quote_ident(tableName) ||
Hi all,
I need use dinamic string for my cursor... My code is:
query varchar := 'field1, field2 from ''' || tableName ||''' ;
//has dinamic params
then I create cursor...
myCursor CURSOR FOR SELECT query;
I compile my function but when i run it I obtain error...
ERROR: invalid inp
On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera
wrote:
> silly escribió:
>> 2009/10/26 Grzegorz Jaśkiewicz :
>> >
>> >
>> > On Mon, Oct 26, 2009 at 10:30 AM, silly wrote:
>> >>
>> >> Suppose that you have a query, say $sql_query, which is very
>> >> complicated and produces many rows. Wh
silly escribió:
> 2009/10/26 Grzegorz Jaśkiewicz :
> >
> >
> > On Mon, Oct 26, 2009 at 10:30 AM, silly wrote:
> >>
> >> Suppose that you have a query, say $sql_query, which is very
> >> complicated and produces many rows. Which of the following is going to
> >> be faser:
> >>
> >> $sql_
2009/10/26 Grzegorz Jaśkiewicz :
>
>
> On Mon, Oct 26, 2009 at 10:30 AM, silly wrote:
>>
>> Suppose that you have a query, say $sql_query, which is very
>> complicated and produces many rows. Which of the following is going to
>> be faser:
>>
>> $sql_query OFFSET 3000 LIMIT 12;
>>
>> or
>>
2009/10/26 Grzegorz Jaśkiewicz :
>
>
> On Mon, Oct 26, 2009 at 10:30 AM, silly wrote:
>>
>> Suppose that you have a query, say $sql_query, which is very
>> complicated and produces many rows. Which of the following is going to
>> be faser:
>>
>> $sql_query OFFSET 3000 LIMIT 12;
>>
>> or
>>
On Mon, Oct 26, 2009 at 10:30 AM, silly wrote:
> Suppose that you have a query, say $sql_query, which is very
> complicated and produces many rows. Which of the following is going to
> be faser:
>
>$sql_query OFFSET 3000 LIMIT 12;
>
> or
>
>BEGIN;
>DECLARE cur1 CURSOR FOR $sql_que
Suppose that you have a query, say $sql_query, which is very
complicated and produces many rows. Which of the following is going to
be faser:
$sql_query OFFSET 3000 LIMIT 12;
or
BEGIN;
DECLARE cur1 CURSOR FOR $sql_query;
MOVE 3000 IN cur1;
FETCH 12 FROM cur1;
COMMIT;
Nat
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Pavel Stehule schrieb:
> Hello
>
> when you would to change name of table - you have to use dynamic query
>
> 38.7.2.2. OPEN FOR EXECUTE
>
> OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string;
>
>
> http://www.postgresql.org/docs/8.3/i
Hello
when you would to change name of table - you have to use dynamic query
38.7.2.2. OPEN FOR EXECUTE
OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string;
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html
regards
Pavel Stehule
2009/2/25 Andreas Wenk :
> -BE
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
short question. Why is this not possible:
CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$
DECLARE
curs1 CURSOR FOR SELECT * FROM $1;
BEGIN
OPEN curs1;
RETURN curs1;
END; $$ LANGUAGE plpgsql;
Or,
Bob Pawley wrote:
> Is it allowed to declare a cursor in this manner??
>
> Declare
> procgraphic cursor for select p_id.p_id.process_id
> from p_id.p_id, processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;
Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
i
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "PostgreSQL"
Sent: Thursday, July 31, 2008 9:50 AM
Subject: Re: [GENERAL] Cursor Error
"Bob Pawley" <[EMAIL PROTECTED]> writes:
Right.
This is the cursor statement.
Open procgraphic for select p_id.p_id.process_i
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Right.
> This is the cursor statement.
> Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
> processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;
Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
--- pe
CTED]>
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "PostgreSQL"
Sent: Thursday, July 31, 2008 9:30 AM
Subject: Re: [GENERAL] Cursor Error
"Bob Pawley" <[EMAIL PROTECTED]> writes:
Could somebody translate this error message for me??
"cursor is no
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Could somebody translate this error message for me??
> "cursor is not simply updateable scan of table "p_id"
You're trying to do an "UPDATE WHERE CURRENT OF cursor", right?
What it means is that the cursor definition is too complicated for
Postgres to fi
Could somebody translate this error message for me??
Bob
"cursor is not simply updateable scan of table "p_id"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Jul 29, 2008, at 4:51 PM, Klint Gore wrote:
It's different in PL/pgSQL.
Ah, yes, sorry, didn't catch that it was a PL/pgSQL function.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Christophe wrote:
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote:
> No, he does need an OPEN.
Really? I thought that PG didn't use OPEN:
"The PostgreSQL server does not implement an OPEN statement for
cursors; a cursor is considered to be open when it is declared."
http://www.postgres
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote:
No, he does need an OPEN.
Really? I thought that PG didn't use OPEN:
"The PostgreSQL server does not implement an OPEN statement for
cursors; a cursor is considered to be open when it is declared."
http://www.postgresql.org/docs/8.3/int
ROTECTED]>
Cc: "Richard Huxton" <[EMAIL PROTECTED]>; "PostgreSQL"
Sent: Tuesday, July 29, 2008 3:30 PM
Subject: Re: [GENERAL] Cursor
"Bob Pawley" <[EMAIL PROTECTED]> writes:
Qualifying the column was the solution.
Huh. What was the error message yo
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Qualifying the column was the solution.
Huh. What was the error message you got, exactly? Because it doesn't
seem like that should have led to a syntax error.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-gen
Yes
Bob
- Original Message -
From: "Adrian Klaver" <[EMAIL PROTECTED]>
To: "Tom Lane" <[EMAIL PROTECTED]>; "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Richard Huxton" <[EMAIL PROTECTED]>; "PostgreSQL&q
Thanks Tom
Qualifying the column was the solution.
Bob
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Richard Huxton" <[EMAIL PROTECTED]>; "PostgreSQL"
Sent: Tuesday, J
-- Original message --
From: Tom Lane <[EMAIL PROTECTED]>
> "Bob Pawley" <[EMAIL PROTECTED]> writes:
> > Following is more complete. The balance of the trigger that is not shown
> > works when tested separately. I didn't include it because it is quite long.
>
> Hm
On Tue, Jul 29, 2008 at 5:42 PM, Bob Pawley <[EMAIL PROTECTED]> wrote:
> begin
Don't you need a ; after your begin...?
--
- David T. Wilson
[EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/m
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Following is more complete. The balance of the trigger that is not shown
> works when tested separately. I didn't include it because it is quite long.
Hmm, I still don't see anything that looks like a syntax error, but
I'll bet this is a name collision r
t;[EMAIL PROTECTED]>
To: "Richard Huxton" <[EMAIL PROTECTED]>
Cc: "Bob Pawley" <[EMAIL PROTECTED]>; "PostgreSQL"
Sent: Tuesday, July 29, 2008 2:35 PM
Subject: Re: [GENERAL] Cursor
Richard Huxton <[EMAIL PROTECTED]> writes:
Bob Pawley wr
Richard Huxton <[EMAIL PROTECTED]> writes:
> Bob Pawley wrote:
>> DECLARE
>> procgraphic cursor for select process_id from p_id.p_id,
>> processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;
>>
>> begin
>>
>> Open procgraphic ;
> There is no OPEN, you just FETCH
No, he does nee
Bob Pawley wrote:
I have the following cursor that gives me an error near open.
Can someone please tell me what I am doing wrong??
DECLARE
procgraphic cursor for select process_id from p_id.p_id,
processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;
begin
Open procgraphic
I have the following cursor that gives me an error near open.
Can someone please tell me what I am doing wrong??
Bob
DECLARE
procgraphic cursor for select process_id from p_id.p_id, processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;
begin
Open procgraphic ;
Fetch f
I understand that a cursor can be opened and used through sql commands.
However, when a host application accesses a particular row on a particular
table can that be detected within the sql medium?
Or does that information have the be accessed by transmitting from the host?
Bob
--
Sent via
Justin wrote:
> Cursor manipulation with select statements
>
> This is something i did in foxpro and wonder if it is at all
> possible in pl/pgSQL.
Yes, it is. See
http://www.postgresql.org/docs/current/static/plpgsql-cursors.html
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgs
Cursor manipulation with select statements
This is something i did in foxpro and wonder if it is at all possible in
pl/pgSQL. I would create a cursor in foxpro then populate it with
Inserts then after all that was done use a Select statement to do more
manipulation if need be
the Foxpro cod
Can somebody help me to appreciate the difference between returning a
setof record with cursor or with for _row in select ... as in
http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html
Once I use and define cursors read only, no scroll, insensi
I wish to display the content of a cursor but haven't discovered the
trick. Here's what I'm doing:
-The function
CREATE OR REPLACE FUNCTION tmp.sps(character varying, date)
RETURNS refcursor AS
$BODY$
DECLARE
ref refcursor;
BEGIN
O
Tim Tassonis <[EMAIL PROTECTED]> writes:
> I try to reproduce the situation tomorrow and will also check on any
> odbc_errmsg() messages and the postmaster log.
OK. A couple of comments: the only timeout within Postgres itself is
statement_timeout, which I think wouldn't apply to your situation
Hi Tom
Tom Lane wrote:
Tim Tassonis <[EMAIL PROTECTED]> writes:
When examining strange behaviour in one of my programs I found out that
I must have somehow gotten into a timeout situation when fetching rows
from a cursor. My program read the first row, did some stuff for six
minutes and then
Tim Tassonis <[EMAIL PROTECTED]> writes:
> When examining strange behaviour in one of my programs I found out that
> I must have somehow gotten into a timeout situation when fetching rows
> from a cursor. My program read the first row, did some stuff for six
> minutes and then tried to fetch the
Hi all
When examining strange behaviour in one of my programs I found out that
I must have somehow gotten into a timeout situation when fetching rows
from a cursor. My program read the first row, did some stuff for six
minutes and then tried to fetch the second row, which failed. The
connecti
Hello all,
I am trying to get the list of all foreign key tables
for a given primary table after getting this list I
want to filter out the name of table for which a
particular Primary table.primary key exsits in the
foreign table.foreign key column.
For that I have written a SP but the first curs
Nigel Horne <[EMAIL PROTECTED]> writes:
> I can't get passed this message:
> 'Cannot mix placeholder styles "$1" and ":foo"'
There's no such message anywhere in the Postgres sources. I suppose it
must be coming from whatever client-side library you are using (which
you didn't say).
Nigel Horne wrote:
How do I declare a cursor in a stored procedure if that cursor contains
a WHERE which depends on the argument given to that stored procedure?
I can't get passed this message:
'Cannot mix placeholder styles "$1" and ":foo"'
The message doesn't give a useful line number, so I
How do I declare a cursor in a stored procedure if that cursor contains
a WHERE which depends on the argument given to that stored procedure?
I can't get passed this message:
'Cannot mix placeholder styles "$1" and ":foo"'
The message doesn't give a useful line number, so I don't even know
which
# [EMAIL PROTECTED] / 2005-08-04 09:56:03 -0700:
> Thanks Roman for sticking with me on this!
> For whatever reason I cannot load another langage, I think it has to do
> with recompiling the program and installing all the options. Not sure
> though?? LANGUAGE plpgsql doesn't exist for me.
Pl
Hey Roman,
Thanks for your reponse's!
I made it happen in MicrosoftSQL using the first code below. The only
difference is I had to create variables. Which I'm having a hard time
trying to replicate it in psql.
__Microsoft Code___
USE test
GO
DECLARE @qty INT, @Length varchar(20
Thanks Roman for sticking with me on this!
For whatever reason I cannot load another langage, I think it has to do
with recompiling the program and installing all the options. Not sure
though?? LANGUAGE plpgsql doesn't exist for me.
I still find this cursor limitation wacked. I find it hard to
# [EMAIL PROTECTED] / 2005-08-02 10:01:34 -0400:
> I made it happen in MicrosoftSQL using the first code below. The only
> difference is I had to create variables. Which I'm having a hard time
> trying to replicate it in psql.
>
> __Microsoft Code___
> USE test
> GO
> DECLARE @
# [EMAIL PROTECTED] / 2005-07-27 12:21:34 -0700:
> I found using the shell works but using the phAdminIII GUI is the one
> that gives me problems. I've even tried running it on EMS PostgreSQL
> Manager 3. Same results. Is this normal?
>
> Got a couple more questions regarding cursors.
> 1. When
I found using the shell works but using the phAdminIII GUI is the one
that gives me problems. I've even tried running it on EMS PostgreSQL
Manager 3. Same results. Is this normal?
Got a couple more questions regarding cursors.
1. When I try to run this statement (Declare curs1 refcursor;) I ge
# [EMAIL PROTECTED] / 2005-07-26 14:06:34 -0700:
> BEGIN WORK;
> DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
> FETCH FIRST FROM cursor1;
> CLOSE cursor1;
> COMMIT WORK;
>
>
> Query result with 1 rows discarded.
> Query returned successfully with no result in 31 ms.
>
Here is a further test:
--
BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST IN cursor1;
INSERT INTO partstemp VALUES (PARTS.QTY, PARTS.LENGTH, PARTS.WIDTH);
CLOSE cursor1;
COMMIT WORK;
--
BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST FROM cursor1;
CLOSE cursor1;
COMMIT WORK;
Query result with 1 rows discarded.
Query returned successfully with no result in 31 ms.
In the "data output" view nothing is returned?
On Tue, 2005-05-17 at 12:49, Joseph Shraibman wrote:
> Scott Marlowe wrote:
>
> > Only if you set transaction isolation to serializable.
>
> So am I getting data that was updated up until the time of the FETCH or
> the DECLARE CURSOR?
The data shouldn't change between the declare and the fetch,
Joseph Shraibman writes:
> I do this:
> BEGIN;
> SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
> DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id =
> d.id AND ... ;
> At the end of the fetching if the number of fetched does not equal the
> number from the SELECT co
Scott Marlowe wrote:
Only if you set transaction isolation to serializable.
So am I getting data that was updated up until the time of the FETCH or
the DECLARE CURSOR?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unreg
On Tue, 2005-05-17 at 12:19, Joseph Shraibman wrote:
> I'm running:
>
> PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
> 20030222 (Red Hat Linux 3.2.2-5)
>
>
> I do this:
>
> BEGIN;
> SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
> DECLARE cname CURSOR FOR SELEC
I'm running:
PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
I do this:
BEGIN;
SELECT count(*) FROM u, d WHERE u.id = d.id AND ... ;
DECLARE cname CURSOR FOR SELECT u.field, d.field FROM u, d WHERE u.id =
d.id AND ... ;
At the end of the f
On Fri, May 06, 2005 at 05:58:11AM -0700, Vidya wrote:
>
> How do I create a cursor in psql .
>
> I have a function which opens a cursor .
> SampleDB=# create function reffunc(refcursor) returns refcursor as $$
> SampleDB$# begin
> SampleDB$# open $1 for select col from test;
> SampleDB$# return
Hello All,
How do I create a cursor in psql .
I have a function which opens a cursor .
SampleDB=# create function reffunc(refcursor) returns refcursor as $$SampleDB$# beginSampleDB$# open $1 for select col from test;SampleDB$# return $1;SampleDB$# end;SampleDB$# $$ language sql;ERROR: syntax err
On Wed, Mar 02, 2005 at 11:15:52AM +0200, Sim Zacks wrote:
> select name,testcursor(testid) from test; --doesn't work
> select name,testcursor(testid) from test where testid=1; -- works (as does
> testid=2 or 3)
If I add "close crs;" before the function returns, I get this:
SELECT name, testcurs
> Always close your cursors.
>
Thanks.
In my real example I had multiple return points and did not close the cursor
before each one.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining c
> If I add "close crs;" before the function returns, I get this:
>
> SELECT name, testcursor(testid) FROM test;
> name | testcursor
> --+
> Bob | -Comment 1-Comment 2-Comment 3
> Mark | -Comment 1
> Tom |
> (3 rows)
>
You got it.
I was closing th
Sim Zacks wrote:
create or replace function testcursor(thistestid int) returns varchar as
$$
declare
crs Cursor for select comments from test a join test2 b on
a.testid=b.testid where a.TestID=thistestid;
thiscomment varchar;
totalstr varchar;
begin
open crs;
fetch crs into thiscomment;
total
I haven't tried 8.0.1 yet. I am planning on making the upgrade soon, but
since I haven't seen this issue discussed I did not think it had been fixed
just because I am using an older version. I did search the Internet and
found one person who had the same issue and found a workaround that won't
appl
On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:
> PostGreSQL 8.0beta1
That's pretty old. Have you tried 8.0.1?
> I have a function that uses a cursor and it is giving me the error: cursor
> "crsr" already in use when the parameters I pass in come from another table.
>
> The function
PostGreSQL 8.0beta1
I have a function that uses a cursor and it is giving me the error: cursor
"crsr" already in use when the parameters I pass in come from another table.
The function works fine when I call it by itself, such as select
PartNeedsReschedule(100,1) or select * from PartNeedsReschedu
On Mon, Jan 17, 2005 at 12:06:56AM -0600, Mike G. wrote:
> It doesn't look like it has been added to the documentation yet. The
> only reference I could find to it was in the todo list (create
> similiar ability for delete statement).
>
> USING allows you to add join statements to your update sta
On Fri, Jan 14, 2005 at 01:28:47PM +0100, Martijn van Oosterhout wrote:
> On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote:
> > thank you.
> >
> > I use the cursor because I really do an update against a different
> > table based on a value from the select in the original table. I am
> > e
On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote:
> thank you.
>
> I use the cursor because I really do an update against a different
> table based on a value from the select in the original table. I am
> eagerly awaiting 8.0 and the ability to issue an Update Table1 Set
> Table1.col = Tab
1 - 100 of 108 matches
Mail list logo
