SET variable from INSERT INTO ... RETURNING

guspower commented 2 months ago

Introduction

I am trying to set a sqlpage variable from the result of an INSERT INTO ... RETURNING statement.

To Reproduce

List of steps to reproduce the behavior. Include the sql file you are using and the eventual relevant parts of your database schema

SET api_exchange_id = (INSERT INTO api_exchange(name) VALUES ('get-access-token') RETURNING api_exchange_id);

Actual behavior

sql parser error: Expected variable value, found: INTO at Line: 4, Column 13
SQLPage could not parse and prepare this SQL statement

Version information

OS: Linux 6.6.13-gentoo
Database: Postgresql 16.2
SQLPage Version: v0.20.2

Additional context

I have successfully managed to SET variables using SELECT queries, e.g.

SET base_url = (SELECT base_url FROM env WHERE name = 'sandbox');

However I am unable to do this using INSERT INTO ... RETURNING style expressions. This means that I have to insert a row and then subsequently query for the id. It would be excellent if I could do that in a single statement to avoid risk of concurrent collision.

lovasoa commented 2 months ago

Hello and welcome to SQLPage ! Unfortunately, SET xxx = INSERT ... is not valid SQL and is not supported by the sql parser. There are several ways to work around that. In you case, since you are using postgres, the easiest is to use currval. There is no risk of collision, since currval guarantees it returns the value last inserted in the current session (even if other values have been inserted in other sessions in the meantime), and SQLPage guarantees that a single query is always handled in a single database session.

guspower commented 2 months ago

Ah - great advice, thank you +1

TIL: currval is session scoped. Excellent.

Am really enjoying sqlpage, it is really refreshing. Thank you for your hard work.

lovasoa commented 2 months ago

In case someone stumbles upon this, but uses a different database, the equivalent

in MySQL is https://dev.mysql.com/doc/refman/8.3/en/information-functions.html#function_last-insert-id
in SQLite is https://www.sqlite.org/lang_corefunc.html#last_insert_rowid
- in Microsoft SQL Server is https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver16

all of them offer the same safety guarantees in face of concurrency.

lovasoa / SQLpage