Re: cannot open multi-query plan as cursor

Tom,

I 'raise notice'd the whole query by the function (which was quoted in my
previous letter).
Actually I generate the query on the fly according to the given parameters.
I call it in a for R in execute Query statement.
As the function is a bit long I quote only a portion of it.
----
FieldList := ''select distinct productid, quantity, date, (select dir
from t_changes where id=changeid) as dir '';
FromList := '' from t_stockchanges '';
JoinList := '' join t_prod_in_pgr using (productid) join
t_productgroups on (t_productgroups.id=productgroupid) '';
WhereList := '' where productid in (select productid from t_prod_in_pgr
where productgroupid = '' || quote_literal(ID) || '') and ''||
''(date between '' || quote_literal(Date1) || '' and '' ||
quote_literal(Date2) || '')'';
-- etc...

Query := FieldList || FromList || JoinList || WhereList;
raise notice ''%'', Query;
for StockChangesRec in execute Query loop
...
end loop
----
AFAIC running the copy-pasted notice from psql console is equivalent with
calling it from a for ... in statement.

Any suggestions or shall I quote the whole function instead?

Thank you very much.

-- Csaba

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: Thursday, August 07, 2003 6:16 PM
To: csegyud(at)vnet(dot)hu
Cc: Pgsql-General(at)Postgresql(dot)Org (E-mail)
Subject: Re: [GENERAL] cannot open multi-query plan as cursor

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud(at)vnet(dot)hu> writes:
> When I copy it into the console (psql) it runs well, but from function it
> sends an error:
> ERROR: cannot open multi-query plan as cursor
> What does it mean? Could anybody help me?

I think most likely it means you made a typo transcribing the query into
the function. If you can't figure it out, show us the whole function
definition ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com)
Version: 6.0.505 / Virus Database: 302 - Release Date: 2003. 07. 30.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com)
Version: 6.0.505 / Virus Database: 302 - Release Date: 2003. 07. 30.