Thread: cursor "x" does not exist
Hello,
My apologies if I'm being incredibly stupid here, but I've reviewed
what the docs have to say about naming portals and I still can't see
where I'm going wrong here ?
CREATE FUNCTION blah(v_id text,v_cursor refcursor) RETURNS refcursor
AS
$BODY$
BEGIN
OPEN v_cursor FOR SELECT * FROM blah where idcol=v_id;
RETURN v_cursor;
END;
$BODY$
LANGUAGE plpgsql;
The select query in here does return data, so I know the output from
below is certainly expected to return something.
BEGIN;
SELECT blah('A','B');
blah
--------------------------
B
(1 row)
FETCH ALL IN B;
ERROR: cursor "b" does not exist
P.S. As a side-question, if anyone here has experience in using
Postgres as a backend to PHP, are refcursors the way to go or should I
be thinking of SETOF or other return styles ?
Bob Jones <r.a.n.d.o.m.d.e.v.4+postgres@gmail.com> writes:
> My apologies if I'm being incredibly stupid here, but I've reviewed
> what the docs have to say about naming portals and I still can't see
> where I'm going wrong here ?
I think you're forgetting to double-quote an upper case identifier.
> FETCH ALL IN B;
> ERROR: cursor "b" does not exist
The cursor is named "B" not "b", but B without quotes folds to the latter.
regards, tom lane
On 14 April 2018 at 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bob Jones <r.a.n.d.o.m.d.e.v.4+postgres@gmail.com> writes: >> My apologies if I'm being incredibly stupid here, but I've reviewed >> what the docs have to say about naming portals and I still can't see >> where I'm going wrong here ? > > I think you're forgetting to double-quote an upper case identifier. > >> FETCH ALL IN B; >> ERROR: cursor "b" does not exist > > The cursor is named "B" not "b", but B without quotes folds to the latter. > > regards, tom lane Thank you tom for showing me the error in my ways. Now where's that dunce cap gone ? Maybe I don't deserve to use anything better than MySQL. ;-)
On 04/14/2018 09:36 AM, Bob Jones wrote: > On 14 April 2018 at 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Bob Jones <r.a.n.d.o.m.d.e.v.4+postgres@gmail.com> writes: >>> My apologies if I'm being incredibly stupid here, but I've reviewed >>> what the docs have to say about naming portals and I still can't see >>> where I'm going wrong here ? >> >> I think you're forgetting to double-quote an upper case identifier. >> >>> FETCH ALL IN B; >>> ERROR: cursor "b" does not exist >> >> The cursor is named "B" not "b", but B without quotes folds to the latter. >> >> regards, tom lane > > > Thank you tom for showing me the error in my ways. > > Now where's that dunce cap gone ? Maybe I don't deserve to use > anything better than MySQL. ;-) Or read the below and be illuminated:) https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS > > -- Adrian Klaver adrian.klaver@aklaver.com
Hello Bob, On Sat, 2018-04-14 at 15:44 +0100, Bob Jones wrote: > Hello, > > > > P.S. As a side-question, if anyone here has experience in using > Postgres as a backend to PHP, are refcursors the way to go or should > I > be thinking of SETOF or other return styles ? > If you have a business requirement along the lines of "display all outstanding orders for a customer" it is best to create a view that serves that purpose. Then all you need to code is a "select * from v_outstanding_orders where customer_id = $1"; If there are any outstanding orders you can page through the associative array returned by the query in order to display the results. HTH, Rob