pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedpass open cursor via USING in execute staement
4+ messages / 4 participants
[nested] [flat]
* pass open cursor via USING in execute staement
@ 2023-05-31 22:57 PG Doc comments form <noreply@postgresql.org>
2023-06-01 14:23 ` Re: pass open cursor via USING in execute staement Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-06 19:48 ` Re: pass open cursor via USING in execute staement Kirk Wolak <wolakk@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: PG Doc comments form @ 2023-05-31 22:57 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: domenico.l76@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/plpgsql-statements.html
Description:
Should be documented if this statement is allowed or not (if not, maybe set
as desired feature :-) )
EXECUTE format('update %I set col1=$1, col2=now() WHERE current of $3',
tabname)
USING myVal, myOpenCursor;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pass open cursor via USING in execute staement
2023-05-31 22:57 pass open cursor via USING in execute staement PG Doc comments form <noreply@postgresql.org>
@ 2023-06-01 14:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-01 16:41 ` Re: pass open cursor via USING in execute staement Lorusso Domenico <domenico.l76@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2023-06-01 14:23 UTC (permalink / raw)
To: domenico.l76@gmail.com; pgsql-docs@lists.postgresql.org
On Wed, 2023-05-31 at 22:57 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/15/plpgsql-statements.html
>
> Should be documented if this statement is allowed or not (if not, maybe set
> as desired feature :-) )
>
> EXECUTE format('update %I set col1=$1, col2=now() WHERE current of $3',
> tabname)
> USING myVal, myOpenCursor;
That is not supported. Parameters can only be used in a place where constants
(SQL literals) might appear, not instead of identifiers (names of tables, columns etc.)
I looked, and that is really not documented anywhere.
One of PREPARE or the extended query protocol might be good places.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pass open cursor via USING in execute staement
2023-05-31 22:57 pass open cursor via USING in execute staement PG Doc comments form <noreply@postgresql.org>
2023-06-01 14:23 ` Re: pass open cursor via USING in execute staement Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-06-01 16:41 ` Lorusso Domenico <domenico.l76@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Lorusso Domenico @ 2023-06-01 16:41 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
Yes, I agree, but also in cursor page.
Talking about cursor I can't find example that shows how pass a bounded
cursor to a function or procedure (must be already open? must be declared
in some specific way?)
Il giorno gio 1 giu 2023 alle ore 16:23 Laurenz Albe <
laurenz.albe@cybertec.at> ha scritto:
> On Wed, 2023-05-31 at 22:57 +0000, PG Doc comments form wrote:
> > Page: https://www.postgresql.org/docs/15/plpgsql-statements.html
> >
> > Should be documented if this statement is allowed or not (if not, maybe
> set
> > as desired feature :-) )
> >
> > EXECUTE format('update %I set col1=$1, col2=now() WHERE current of $3',
> > tabname)
> > USING myVal, myOpenCursor;
>
> That is not supported. Parameters can only be used in a place where
> constants
> (SQL literals) might appear, not instead of identifiers (names of tables,
> columns etc.)
>
> I looked, and that is really not documented anywhere.
> One of PREPARE or the extended query protocol might be good places.
>
> Yours,
> Laurenz Albe
>
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pass open cursor via USING in execute staement
2023-05-31 22:57 pass open cursor via USING in execute staement PG Doc comments form <noreply@postgresql.org>
@ 2023-06-06 19:48 ` Kirk Wolak <wolakk@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Kirk Wolak @ 2023-06-06 19:48 UTC (permalink / raw)
To: domenico.l76@gmail.com; pgsql-docs@lists.postgresql.org
On Thu, Jun 1, 2023 at 9:32 AM PG Doc comments form <noreply@postgresql.org>
wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/plpgsql-statements.html
> Description:
>
> Should be documented if this statement is allowed or not (if not, maybe set
> as desired feature :-) )
>
> EXECUTE format('update %I set col1=$1, col2=now() WHERE current of $3',
> tabname)
> USING myVal, myOpenCursor;
>
Well, you can handle this by naming the cursor specifically, based on the
table that should be updated.
Then using the name of myOpenCursor.
and you use a refcursor variable to pass them around.
You name them like this, before opening them:
cCursor := 'my_cursor_update_table3';
Now usually the name matches the incoming variable name. You are going
to have to play with this.
Later in your code, you can test the value of the cursor name by simply
doing:
RAISE NOTICE 'Cursor Name: %', cCursor;
So you can copy it to a variable, and compare it. Then determine the
table to apply the update to.
That's the best I can come up with...
Kirk Out!
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-06-06 19:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-31 22:57 pass open cursor via USING in execute staement PG Doc comments form <noreply@postgresql.org>
2023-06-01 14:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-06-01 16:41 ` Lorusso Domenico <domenico.l76@gmail.com>
2023-06-06 19:48 ` Kirk Wolak <wolakk@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox