using PREPAREd statements in CURSOR

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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