Re: Cursor Issue??

From: Roman Neuhauser <neuhauser(at)sigpipe(dot)cz>
To: DracKewl <bradbudge(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Cursor Issue??
Date: 2005-08-05 12:34:22
Message-ID: 20050805123422.GA56217@isis.sigpipe.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

# bradbudge(at)hotmail(dot)com / 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.

Please send the output of this command:

createlang -U postgres -d $dbname plpgsql

> I still find this cursor limitation wacked. I find it hard to believe
> that nobody else is requiring this curosr funcionality. Why else have
> a cursor?

You are trying to use a cursor in interactive SQL. SQL99 doesn't
allow that at all!

> See the bottom from this link
> http://www.postgresql.org/docs/7/interactive/sql-fetch.htm Even in
> there is display of a teaser that it can be done. Too bad there is no
> example.

It says:

: Compatibility
: SQL92
:
: Note: The non-embedded use of cursors is a Postgres extension.
: The syntax and usage of cursors is being compared against the
: embedded form of cursors defined in SQL92.
:
: SQL92 allows absolute positioning of the cursor for FETCH, and
: allows placing the results into explicit variables.
:
: FETCH ABSOLUTE #
: FROM cursor
: INTO :variable [, ...]

The text above is valid for SQL:1999 as well.

You fall in the non-embedded category, IOW that insufficient
functionality you are trying to use is outside the SQL standard.

The example servers to document the paragraph that begins "SQL92
allows (...)", IOW what you see is the SQL92, Embedded SQL syntax,
not what you can do in PostgreSQL.

> My objective is to look at each record one at at time from top to
> bottom. I need to take that information in variable form, and run it
> through a routine that is in the cursor block, then the end result
> needs to end up in another table. There will be times where I will
> also need to scroll forward and backward.

To me the fact that the interactive FETCH can only *display* the
row, while PL/PGSQL is always NO SCROLL and you can only FETCH NEXT
on it, looks like a bad combination of features and shortcomings.

Maybe you'd like to take this to the pgsql-docs@ list? At least
the more knowledgable people could add some real life (= nontrivial)
examples.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2005-08-05 12:45:50 Re: How to write jobs in postgresql
Previous Message John Wells 2005-08-05 12:25:18 Optimizing large data loads