On Fri, Nov 25, 2022 at 10:06 PM Kirk Wolak <wol...@gmail.com> wrote:
> On Fri, Nov 25, 2022 at 9:58 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Fri, Nov 25, 2022 at 12:40 AM Laurenz Albe <laurenz.a...@cybertec.at>
>> wrote:
>>
>>> On Thu, 2022-11-24 at 15:50 -0500, Kirk Wolak wrote:
>>>
>> ..
> Regardless of the above choice for the example, it seems appropriate for
>> this page, somewhere, to mention this function and link to its page.
>>
>> I'd even argue for moving that function definition here.
>>
>> David J.
>>
>
> Okay, I've really reworked the example, and it all tests out.
> I took the advice of Laurenz about the separate section outside the table.
> I did not move the function, it seemed alphabetical where it was (easy
> enough to move), but I did use that function twice!
>
> Break out the RED ink and let me know what you think!
>
> Kirk
>
Okay, forgive me again. I've figured out how to rebase against the latest
master. There are no changes from the previous post.
But the diff file contains updated references (I have no idea if this
helps, but I was unable to "view" someone elses file with diffs without
that).
Laurenz,
Your feedback would be appreciated. If we keep the "play" schema, I
think a comment at the end of the section reminding them to DROP that
schema would be nice.
Kirk
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 82fba48d5f..360cb48f70 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17625,6 +17625,11 @@ $.* ? (@ like_regex "^\\d+$")
command.
</para>
<para>
+<programlisting>
+SELECT nextval('myseq'::regclass);
+</programlisting>
+ </para>
+ <para>
This function requires <literal>USAGE</literal>
or <literal>UPDATE</literal> privilege on the sequence.
</para></entry>
@@ -17657,11 +17662,11 @@ $.* ? (@ like_regex "^\\d+$")
Furthermore, the value reported by <function>currval</function> is not
changed in this case. For example,
<programlisting>
-SELECT setval('myseq', 42); <lineannotation>Next
<function>nextval</function> will return 43</lineannotation>
-SELECT setval('myseq', 42, true); <lineannotation>Same as
above</lineannotation>
-SELECT setval('myseq', 42, false); <lineannotation>Next
<function>nextval</function> will return 42</lineannotation>
+SELECT setval('myseq', 42); <lineannotation>-- The next
<function>nextval</function>('myseq') will return 43</lineannotation>
+SELECT setval('myseq', 42, true); <lineannotation>-- Same as
above</lineannotation>
+SELECT setval('myseq', 42, false); <lineannotation>-- The next
<function>nextval</function>('myseq') will return 42</lineannotation>
</programlisting>
- The result returned by <function>setval</function> is just the value
of its
+ The result returned by <function>setval</function> is the value of its
second argument.
</para>
<para>
@@ -17669,7 +17674,6 @@ SELECT setval('myseq', 42, false);
<lineannotation>Next <function>nextval</fu
sequence.
</para></entry>
</row>
-
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -17686,6 +17690,9 @@ SELECT setval('myseq', 42, false);
<lineannotation>Next <function>nextval</fu
returning a session-local value, it gives a predictable answer whether
or not other sessions have executed <function>nextval</function> since
the current session did.
+<programlisting>
+SELECT currval('myseq'::regclass);
+</programlisting>
</para>
<para>
This function requires <literal>USAGE</literal>
@@ -17707,19 +17714,75 @@ SELECT setval('myseq', 42, false);
<lineannotation>Next <function>nextval</fu
identical to <function>currval</function>, except that instead
of taking the sequence name as an argument it refers to whichever
sequence <function>nextval</function> was most recently applied to
- in the current session. It is an error to call
- <function>lastval</function> if <function>nextval</function>
- has not yet been called in the current session.
+ in the current session. (An error is reported if
<function>nextval</function> has
+ never been called in this session.)
+<programlisting>
+SELECT lastval();
+</programlisting>
</para>
<para>
This function requires <literal>USAGE</literal>
or <literal>SELECT</literal> privilege on the last used sequence.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry">
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
+ <para>Example
+<programlisting>
+CREATE SCHEMA play; <lineannotation>-- Create a
play schema</lineannotation>
+SET search_path = play; <lineannotation>-- Make sure we
create this in the play schema</lineannotation>
+
+CREATE SEQUENCE test_seq;
+
+SELECT nextval('test_seq'::regclass); <lineannotation>--
1</lineannotation>
+SELECT currval('test_seq'); <lineannotation>--
1</lineannotation>
+SELECT lastval(); <lineannotation>--
1</lineannotation>
+<lineannotation>-- If you want to see this sequence in psql</lineannotation>
+\ds test_seq
+<lineannotation>-- If you want to see all sequences in psql</lineannotation>
+\ds
+
+<lineannotation>-- Using the DEFAULT value you can assign this SEQUENCE to be
used when the field is not assigned a value</lineannotation>
+CREATE TABLE t1 (id bigint NOT NULL DEFAULT nextval('test_seq'), other_data
text); <lineannotation>-- links column/sequence</lineannotation>
+
+INSERT INTO t1 (other_data) VALUES ('Some Data'); <lineannotation>-- Assigns
the next ID automatically</lineannotation>
+INSERT INTO t1 (other_data) VALUES ('Some Data')
+ RETURNING id; <lineannotation>-- Assigns
the next ID, and returns it to you!</lineannotation>
+
+INSERT INTO t1 (id, other_data) VALUES (NULL, 'Some Data');
+<lineannotation>-- Oops, you forced the ID to NULL: error violates not-null
constraint</lineannotation>
+
+INSERT INTO t1 (id, other_data) VALUES (nextval('test_seq'), 'Some Data')
+ RETURNING id; <lineannotation>--
Redundant, but useful in some ETL</lineannotation>
+
+<lineannotation>-- If you create a table with the GENERATED syntax, a sequence
is generated behind the scenes</lineannotation>
+
+CREATE TABLE t2 ( id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
other_data text);
+
+<lineannotation>-- An implicit sequence named t2_id_seq is
created</lineannotation>
+
+INSERT INTO t2 (other_data) values ('data') RETURNING id; <lineannotation>--
1</lineannotation>
+SELECT currval('t2_id_seq'::regclass); <lineannotation>--
1</lineannotation>
+
+SELECT setval('t2_id_seq', 10);
+INSERT INTO t2 (other_data) values (1234) RETURNING id; <lineannotation>--
11</lineannotation>
+
+SELECT lastval(); <lineannotation>--
11</lineannotation>
+
+<lineannotation>-- Finally, how can you determine the sequence name used for a
GENERATED SEQUENCE?</lineannotation>
+SELECT pg_get_serial_sequence('t2', 'id');
+
+<lineannotation>-- And therefore you can use it with the functions in this
section, without knowing it's name</lineannotation>
+SELECT currval(pg_get_serial_sequence('t2', 'id'));
+
+</programlisting>
+ </para>
<caution>
<para>
To avoid blocking concurrent transactions that obtain numbers from
