| From: | Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com> | 
|---|---|
| To: | Postgres General <pgsql-general(at)postgresql(dot)org> | 
| Subject: | using PREPAREd statements in CURSOR | 
| Date: | 2007-06-28 08:20:12 | 
| Message-ID: | 895E99B0-5BD3-4D7F-BE23-AE8DC9771939@gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hello!
I'm connecting via libpq and want to
use prepared statements in a cursor.
Is there a sample somewhere, since I cannot get
it to work.
sebjlun=# \d ssignal
        Table "public.ssignal"
  Column  |     Type      | Modifiers
---------+---------------+-----------
  ssignam | character(12) | not null
  ssigdes | character(30) | not null
  ssopcid | character(40) |
  sstatyp | integer       | not null
  sstatid | integer       | not null
  ssigtyp | integer       | not null
  ssigadd | integer       | not null
  ssigran | integer       | not null
Indexes:
     "ssignalp1" PRIMARY KEY, btree (ssignam)
     "ssignali3" btree (sstatyp, sstatid)
sebjlun=# prepare test as
sebjlun-# select * from SSIGNAL where SSTATYP=$1 and SSTATID=$2 order  
by SSIGNAM
sebjlun-# ;
PREPARE
sebjlun=# execute test(4,6);
    ssignam    |            ssigdes             |                  
ssopcid
           | sstatyp | sstatid | ssigtyp | ssigadd | ssigran
--------------+-------------------------------- 
+--------------------------------
----------+---------+---------+---------+---------+---------
  CD_PLC_SS    | Counter delay output PLC       |
           |       4 |       6 |       2 |    2103 |      16
  CD_SS_PLC    | Counter delay input SS         |
           |       4 |       6 |       2 |    2003 |      16
  CN_PLC_SS    | Counter number output PLC      |
           |       4 |       6 |       2 |    2102 |      16
  CN_SS_PLC    | Counter Number input SS        |
           |       4 |       6 |       2 |    2002 |      16
  ....
so far so good
sebjlun=# declare cursor ctest for test(4,6);
ERROR:  syntax error at or near "ctest"
LINE 1: declare cursor ctest for test(4,6);
                        ^
sebjlun=#
How would I express that?
Combining cursors with parameter seems not to be the way either
sebjlun=# Declare C240 cursor for select * from SSIGNAL
where SSTATYP=$1 and SSTATID=$2 order by SSIGNAM;
ERROR:  there is no parameter $1
sebjlun=#
/Björn
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rafal Pietrak | 2007-06-28 08:44:46 | Re: a JOIN on same table, but 'slided over' | 
| Previous Message | Martijn van Oosterhout | 2007-06-28 08:07:42 | Re: AutoVacuum Behaviour Question |