PostgreSQL通过一个查询增加表的顺序 [英] PostgreSQL increase a table's sequence with one query

问题描述

我想将以下两个查询合并为一个:

I want to unite the following two queries into one:

SELECT pg_get_serial_sequence('purchase_orders', 'id');
SELECT setval('purchase_orders_id_seq', 30000);

但是如果我将上层SELECT放到setval的第一个参数中，我会得到:

But if I place the upper SELECT into the setval's first parameter I get:

SELECT setval(SELECT pg_get_serial_sequence('purchase_orders', 'id'), 30000);

ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 15

如何将选择的结果("purchase_orders_id_seq")传递给setval?

How can I pass on the select's result ("purchase_orders_id_seq") for setval?

原因是这样的；我想将它像一个函数一样使用，用户只需要输入表的名称和将要设置顺序的数字即可.

The reason for this is that; I want to use it like a function where a user only have to enter the table's name and a number to where sequence will be set.

FUNCTION set_id_sequence(TEXT table_name, INTEGER sequence);

推荐答案

如果要将子查询结果作为函数参数传递，则需要在其周围加上括号:

If you want to pass a subquery result as a function argument, you need parentheses around it:

SELECT setval((SELECT pg_get_serial_sequence('purchase_orders', 'id')), 30000);

但是在这种情况下，SELECT是多余的；您可以直接调用该函数:

But in this case, the SELECT is redundant; you can invoke the function directly:

SELECT setval(pg_get_serial_sequence('purchase_orders', 'id'), 30000);

这篇关于PostgreSQL通过一个查询增加表的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！