Re: cursors and function question - Mailing list pgsql-general
| From | armand pirvu |
|---|---|
| Subject | Re: cursors and function question |
| Date | |
| Msg-id | CFD427B2-8A62-4F37-821F-AAF67D7E09FC@gmail.com Whole thread Raw |
| In response to | Re: cursors and function question (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: cursors and function question
|
| List | pgsql-general |
On Feb 13, 2018, at 12:54 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 02/13/2018 10:22 AM, armand pirvu wrote:Hi
Is there any elegant way not a two steps way I can output the cursor value at each step?
testtbl table has this content
col1 | col2 | col3
------------+------------+------
E1 | CAT1 | 0
E1 | CAT2 | 0
E1 | CAT3 | 0
E4 | CAT1 | 0
E5 | CAT1 | 0
E6 | CAT1 | 0
E7 | CAT1 | 0
This works
BEGIN WORK;
DECLARE fooc CURSOR FOR SELECT * FROM testtbl;
FETCH ALL FROM fooc;
CLOSE fooc;
COMMIT WORK;
col1 | col2 | col3
------------+------------+------
E1 | CAT1 | 0
E1 | CAT2 | 0
E1 | CAT3 | 0
E4 | CAT1 | 0
E5 | CAT1 | 0
E6 | CAT1 | 0
E7 | CAT1 | 0
But
CREATE OR REPLACE FUNCTION foofunc()
RETURNS text AS $$
DECLARE
var2 RECORD;
cur CURSOR FOR SELECT * from testtbl;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO var2;
return var2;
END LOOP;
CLOSE cur;
END; $$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.foofunc()
RETURNS SETOF testtbl
LANGUAGE sql
AS $function$
SELECT * FROM testtbl;
$function$
test=> select * from foofunc();
col1 | col2 | col3
------+------+------
E1 | CAT1 | 0
E1 | CAT2 | 0
E1 | CAT3 | 0
E4 | CAT1 | 0
E5 | CAT1 | 0
E6 | CAT1 | 0
E7 | CAT1 | 0
(7 rows)select foofunc();
foofunc
-------------------------------
("E1 ","CAT1 ",0)
But I am looking to get
foofunc
-------------------------------
("E1 ","CAT1 ",0)
("E1 ","CATs ",0)
etc
Many thanks
— Armand
--
Adrian Klaver
adrian.klaver@aklaver.com
Thanks Adrian
That one I figured it out as well. The idea is that said table has some records which I need to loop and do some processing using cursors similar with
DECLARE
cur CURSOR FOR SELECT *
FROM testtbl FOR UPDATE;
BEGIN
FOR row IN cur LOOP
UPDATE testtbl
SET col3=1
WHERE CURRENT OF cur;
END LOOP;
return cur;
END
For a row update the goal is to return the cursor value be it before/after the update, hence my question and test
I found some code which seems to do what I need but it involves two functions
CREATE or replace FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
And this is what beats me , aka can I put all in one / how ?
Thanks
Armand
pgsql-general by date: