[SQL] Unnamed Cursor return

[Kumar]

Dear Friends,
 
Using Postgres 7.3.4 on Linux 7.3 Server. Using PgAdmin II for 
Windows version 1.6.0 to connect to the server from my client 
machine.
 
CREATE FUNCTION selfn() RETURNS refcursor AS 'DECLARE ref1 
refcursor;BEGINOPEN ref1 FOR SELECT *  FROM 
address;RETURN ref1;END;'  LANGUAGE 'plpgsql' 
VOLATILE;
 
this works fine. 
I execute it at PgAdmin
 
SELECT selfn(); 
an unnamed cursor is returned.
fetch all from  "";
at PgAdmin shows a popup window saying the query is executed, 
but the content is not shown.
 
at command prompt
SELECT selfn();

an unnamed cursor is returned.
fetch all from  "";
WARNING:  PerformPortalFetch: portal "" not 
foundFETCH 0
 
(1) pls have a look in the function, that I have named the cursor as ref1, 
but again it is returning a unnamed cursor?
 
(2) How to fetch the content of the unnamed cursor at PgAdmin and at 
Command prompt?
 
 
Please help me with this, as it is much helpful for my development 
purpose.
 
Regards
Kumar

[SQL] unsubscribe

[Rute Solipa]

 
- Original Message - 
From: rute solipa 
To: [EMAIL PROTECTED] 
Sent: Thursday, July 31, 2003 9:35 AM
Subject: [SQL] Unsubscribe

Unsubscribe

[SQL] Fw: Change column data type

[Kumar]

 
Dear Friends,
 
Using Postgres 7.3.4 over the linux server 7.3. 
 
Is it possible to alter/change the data type of a existing 
table's column, with out dropping and recreating a column of same 
name.
 
Thanks for ur knowledge sharing.
 
 
Regards
Kumar
 

Re: [SQL] [BUGS] session variable

[sad]

On Tuesday 02 September 2003 16:40, you wrote:

> No problem to use  a temp table in a trigger (which is local to the
> session), I do so myself. Still, session variables would be nice, making
> coding a little bit more comfortable.

(it would be very good if you implement session variables in PostgreSQL.)

The first problem using temp table is:

CREATE TABLE locals (name text, value text);

CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';

SELECT test_locals_access() ;

column
-


CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');

SELECT test_locals_access() ;

column
-
   <=== the Function seing global table

SELECT value FROM locals WHERE name='n1';

value
-
xxx



the second problem may be resolved with your advise.
look:

i want to log operations on the data in some tables.
so i declared sufficient triggers which write to the log-table.

and now i want to mark each log-record with the "operator_id"
(e.g. to log who made an update)

what possible ways are there ?

if i use temp table to inform the triggers about "operator_id"
then i have problems in manual updates of these tables
(temp table in my session needed too)



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Unnamed Cursor return

[Tom Lane]

"Kumar" <[EMAIL PROTECTED]> writes:
> (1) pls have a look in the function, that I have named the cursor as ref1, =
> but again it is returning a unnamed cursor?

This is not a bug.  See the "returning cursors" section of the plpgsql
manual.  (IIRC, you can also create a named cursor by binding the cursor
in DECLARE.)

> (2) How to fetch the content of the unnamed cursor at PgAdmin and at Comman=
> d prompt?

You probably need a BEGIN block in the psql case.  Can't help you with
pgadmin.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster