Обсуждение: cannot open multi-query plan as cursor

Hi,
I try to run a function which calls this query: (RH7.1 and Cygwin;
PSQL7.3.2; Odbc; Windows (XP&ME&9x) clients)
----
select distinct productid, quantity, date, (select dir from t_changes where
id=changeid) as dir  from t_stockchanges join t_prod_in_pgr using
(productid) join t_productgroups on (t_productgroups.id=productgroupid)
where productid in (select productid from t_prod_in_pgr where productgroupid
= '2') and (date between ' 2003.06.07' and '2003.08.07') and
(t_stockchanges.stockid in (1)) and (productid in (select id from t_products
where id in (select productid from t_prod_in_pgr where productgroupid in
(30,26,4,2,6,7,24,25,21,29,17,23,27,28,20,22)))) and
(t_stockchanges.changeid in (7,3)) and ((prooftype = 'INV'and proofid in
(select id from t_invoices where partnerid in
(31,7,18,21,33,8,19,27,28,9,12,32,10,24,16,13,29,26,22,20,14,23,25,17,15,11,
30))) or  (prooftype = 'DSPN'and proofid in (select id from t_dispatchnotes
where partnerid in
(31,7,18,21,33,8,19,27,28,9,12,32,10,24,16,13,29,26,22,20,14,23,25,17,15,11,
30))));
---

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?

Thanks a lot,

-- Együd Csaba



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

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Thursday, August 07, 2003 6:16 PM
To: csegyud@vnet.hu
Cc: Pgsql-General@Postgresql.Org (E-mail)
Subject: Re: [GENERAL] cannot open multi-query plan as cursor


=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.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.