Cannot open SELECT query as cursor

Question

I am trying to construct a string from a table. I need to construct a string from each row and then concatenate these strings (with an appropriate separator) together. In a simple example the select produces a table with 3 rows; however, I get the message above.

Can anyone help? Thanks

for mFiles in 
    select url || pth || basename || '/' || basename || '.2.' || clientname into fName from files 
       where ( userId is null or uid != userId ) and (url is not null) and (pth is not null) 
          order by RANDOM() limit nImages LOOP
       res := res || fName || '|';
     RAISE NOTICE ' fName: % ' , fName;
   END LOOP;

What's your PostgreSQL version? Odd issue. I haven't looked into it or tested, but if I had to make a wild guess I'd be wondering if the limit clause was the issue... — Craig Ringer
– Craig Ringer, Commented Dec 30, 2014 at 1:10

LenB · Accepted Answer · 2014-12-30 13:00:59Z

2

The problem was 'into fName'. Replacing this by 'as fName' and writing res:= res || mFiles.fName || '|';

fixed the problem. Don't understand why exactly but ...

answered Dec 30, 2014 at 13:00

LenB

1,2024 gold badges16 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

khampson · Accepted Answer · 2014-12-30 03:39:50Z

0

Following from Craig Ringer's suggestion, what if you remove the limit clause and instead break out of the loop with code like this (which would both test whether limit is the culprit and provide a good workaround):

idx := 1
for mFiles in 
    select url || pth || basename || '/' || basename || '.2.' || clientname into fName from files 
    where ( userId is null or uid != userId ) and (url is not null) and (pth is not null) 
    order by RANDOM()
    LOOP
        res := res || fName || '|';
        RAISE NOTICE ' fName: % ' , fName;

        IF idx = nImages THEN
            EXIT
        END IF;
    END LOOP;

With the addition of declaring idx in the appropriate section higher up in the code.

answered Dec 30, 2014 at 3:39

khampson

15.5k4 gold badges45 silver badges44 bronze badges

Collectives™ on Stack Overflow

Cannot open SELECT query as cursor

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related