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:
>> >
>> > David, let me see how that looks. From an approach standpoint, I am
>> hearing:
>>
> I think it would be better to create the sequence explicitly and use
>> it in the DEFAULT clause of a column definition.
>>
>
> I wasn't too happy with that comment when I wrote it either.
>
> I would probably do without the DEFAULT if going the explicit route (but
> it isn't a deal breaker).
>
> However, I remembered that we have:
>
> pg_get_serial_sequence ( table text, column text ) → text
>
> I'd be inclined to stay with the GENERATED example but incorporate that
> function call into the other examples.
>
> 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
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
