CODEWITHSUNDEEP
databasepostgresqlselectplpgsqldatabase-cursor
LenB
LenB

Reputation: 1172

Cannot open SELECT query as cursor

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;

Upvotes: 3

Views: 11050

Answers (2)

LenB
LenB

Reputation: 1172

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 ...

Upvotes: 2

khampson
khampson

Reputation: 15306

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.

Upvotes: 0

Related Questions