subject:"cursors and function question"

Re: cursors and function question

2018-02-13 Thread armand pirvu


> On Feb 13, 2018, at 4:37 PM, David G. Johnston  
> wrote:
> 
> On Tue, Feb 13, 2018 at 3:31 PM, Adrian Klaver  > wrote:
> 2) By global table do you mean a temporary table? If so not sure that is 
> going to work as I am pretty sure it will disappear after the function is run.
> 
> Temporary tables can survive until either session or transaction end - 
> neither of which occurs automatically when exiting a function.
> 
> https://www.postgresql.org/docs/10/static/sql-createtable.html 
> 
> 
> "Temporary tables are automatically dropped at the end of a session, or 
> optionally at the end of the current transaction (see ON COMMIT below)"
> 
> David J.
> 

Thank you both of you Adrian and David for the input and help

I owe you some nice red wine :)


Cheers
- Armand

Re: cursors and function question

2018-02-13 Thread David G. Johnston

On Tue, Feb 13, 2018 at 3:31 PM, Adrian Klaver 
wrote:

> 2) By global table do you mean a temporary table? If so not sure that is
> going to work as I am pretty sure it will disappear after the function is
> run.

Temporary tables can survive until either session or transaction end -
neither of which occurs automatically when exiting a function.

https://www.postgresql.org/docs/10/static/sql-createtable.html

"Temporary tables are automatically dropped at the end of a session, or
optionally at the end of the current transaction (see ON COMMIT below)"

David J.

Re: cursors and function question

2018-02-13 Thread Adrian Klaver


On 02/13/2018 01:25 PM, armand pirvu wrote:


On Feb 13, 2018, at 1:22 PM, Adrian Klaver > wrote:






Not a trigger , but the idea is we will do some batch processing from 
said table let’s name it testtbl


1 - we get the records using  select for update with a limit 100 for example
2 - update each record using using cursor
3 - print the cursor content so that way I have an idea what was updated

I was thinking that if I can put a unique constraint on the table, I can 
generate a global table in the function , update main table from global 
table and return select from global table


Not entirely sure I know what you are trying to accomplish, still:

1) Not sure you need to use cursor, see here for less complicated way:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

and

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Using RETURN NEXT.

Keeping mind:

https://www.postgresql.org/docs/10/static/plpgsql-cursors.html
"Rather than executing a whole query at once, it is possible to set up a 
cursor that encapsulates the query, and then read the query result a few 
rows at a time. One reason for doing this is to avoid memory overrun 
when the result contains a large number of rows. (However, PL/pgSQL 
users do not normally need to worry about that, since FOR loops 
automatically use a cursor internally to avoid memory problems.) A more 
interesting usage is to return a reference to a cursor that a function 
has created, allowing the caller to read the rows. This provides an 
efficient way to return large row sets from functions."


So if you are keeping the rows to 100 a FOR loop would seem to suffice.

2) By global table do you mean a temporary table? If so not sure that is 
going to work as I am pretty sure it will disappear after the function 
is run. I could see having a permanent table that you INSERT the updated 
rows into with a timestamp. Then you could update the main table from 
that and prune old records using the timestamps.





I can see the developer desire to use cursors to minimize some effort on 
his side


Thanks

Armand





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: cursors and function question

2018-02-13 Thread armand pirvu


> On Feb 13, 2018, at 1:22 PM, Adrian Klaver  wrote:
> 
> On 02/13/2018 11:17 AM, armand pirvu wrote:
>>> On Feb 13, 2018, at 12:54 PM, Adrian Klaver >> >> >> 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.kla...@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
> 
> Not following, are you looking to do this in an UPDATE trigger or somewhere 
> else?
> 
> Another way to ask is why do you want to use a cursor?
> 
>> 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
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 

Not a trigger , but the idea is we will do some batch processing from said 
table let’s name it testtbl

1 - we get the records using  select for update with a limit 100 for example
2 - update each record using using cursor
3 - print the cursor content so that way I have an idea what was updated

I was thinking that if I can put a unique constraint on the table, I can 
generate a global table in the function , update main table from global table 
and return select from global table

I can see the developer desire to use cursors to minimize some effort on his 
side

Thanks 

Armand

Re: cursors and function question

2018-02-13 Thread Adrian Klaver


On 02/13/2018 11:17 AM, armand pirvu wrote:


On Feb 13, 2018, at 12:54 PM, Adrian Klaver > 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.kla...@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


Not following, are you looking to do this in an UPDATE trigger or 
somewhere else?


Another way to ask is why do you want to use a cursor?



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












--
Adrian Klaver
adrian.kla...@aklaver.com

Re: cursors and function question

2018-02-13 Thread armand pirvu


> On Feb 13, 2018, at 12:54 PM, Adrian Klaver  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.kla...@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

Re: cursors and function question

2018-02-13 Thread David G. Johnston

On Tue, Feb 13, 2018 at 12:03 PM, armand pirvu 
wrote:

>
> ERROR:  RETURN cannot have a parameter in function returning set
> LINE 10:  return  var2;
> HINT:  Use RETURN NEXT or RETURN QUERY.
>
>
> and it just sits there
>
> Any hints ?
>
>
https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING


David J.

Re: cursors and function question

2018-02-13 Thread armand pirvu


> On Feb 13, 2018, at 12:26 PM, David G. Johnston  
> wrote:
> 
> On Tuesday, February 13, 2018, armand pirvu  > wrote:
> 
> CREATE OR REPLACE FUNCTION foofunc()
>RETURNS text AS $$
> 
> select foofunc();
> foofunc
> ---
>  ("E1","CAT1  ",0)
> 
> But I am looking to get
> 
> foofunc
> ---
>  ("E1","CAT1  ",0)
>  ("E1","CATs  ",0)
> 
> 
> You need to specify SETOF
> 
> CREATE FUNCTION foofunc() RETURNS SETOF text AS
> 
> David J.

Thank you but


CREATE OR REPLACE FUNCTION foofunc()
   RETURNS setof 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;

ERROR:  RETURN cannot have a parameter in function returning set
LINE 10:  return  var2;
HINT:  Use RETURN NEXT or RETURN QUERY.


so I employed next



CREATE OR REPLACE FUNCTION foofunc()
   RETURNS setof  text AS $$
DECLARE 
 var2   text;
 cur  CURSOR FOR SELECT col1 from testtbl;
BEGIN
   OPEN cur;
LOOP
  FETCH cur INTO var2;
 return next var2;
   END LOOP;
   CLOSE cur;
END; $$
LANGUAGE plpgsql;



and it just sits there

Any hints ?


Thank you
— Armand

Re: cursors and function question

2018-02-13 Thread Adrian Klaver


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.kla...@aklaver.com

Re: cursors and function question

2018-02-13 Thread David G. Johnston

On Tuesday, February 13, 2018, armand pirvu  wrote:

>
> CREATE OR REPLACE FUNCTION foofunc()
>RETURNS text AS $$
>
> select foofunc();
> foofunc
> ---
>  ("E1","CAT1  ",0)
>
> But I am looking to get
>
> foofunc
> ---
>  ("E1","CAT1  ",0)
>  ("E1","CATs  ",0)
>
>
You need to specify SETOF

CREATE FUNCTION foofunc() RETURNS SETOF text AS

David J.

cursors and function question

2018-02-13 Thread armand pirvu

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;


select foofunc();
foofunc
---
 ("E1","CAT1  ",0)

But I am looking to get 

foofunc
---
 ("E1","CAT1  ",0)
 ("E1","CATs  ",0)
etc 



Many thanks
— Armand

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

Re: cursors and function question

cursors and function question

11 matches

Site Navigation

Mail list logo

Footer information